[Power BI] A table of multiple values was supplied where a single value was expected.

Problem

當更新 open data 後，出現以下錯誤

Root Cause

因為有三筆空氣最佳的縣市，即花蓮縣、台東縣、宜蘭縣的 AQI 皆為 36。原始寫法如下：

即使 TOPN 設定 1，仍有可能回傳超過一筆資料，故須改寫。

TOPN usually should return a table with only N rows. However, in case of ties, it bring all ties. This means TopN of 1 might have more than 1 rows if there are two items with the same expression results in the top 1 list. Here is an example;

How-To

由於有三筆資料的 AQI 值相同，取其中一筆來顯示即可。語法改寫如下：

空氣品質最佳城市 = 
var best_aqi_cities = TOPN(1, 'AQI', 'AQI'[平均 AQI],ASC)
var best_aqi_city = TOPN(1, best_aqi_cities, 'AQI'[縣市], ASC)
return SELECTCOLUMNS(best_aqi_city, "Best AQI City", 'AQI'[縣市]&" "&'AQI'[平均 AQI])

檢視結果：