Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I am trying to write a dax to rank the Top3 Model based on there Trust Value.
Here the Trust is Avg Value By Model across all Cluster so i get duplicate rows and hence i thought i will use Summarize to Group the Table by Cluster, Model and Trust and pick top3. I get an error though using the below dax
"Multiple column Cannot be convereted to scalar value"
Model | Cluster | AvgtrustByModel |
Arima | A | 50 |
Arima | B | 50 |
Arima | C | 50 |
Ets | A | 40 |
Ets | B | 40 |
Ets | C | 40 |
Prophet | A | 60 |
Prophet | B | 60 |
Prophet | C | 60 |
TOP3Model1 =
FILTER(SUMMARIZECOLUMNS('Submission KPIs'[Cluster_long], 'Submission KPIs'[Model_Name], "AvgRankByModel", AVERAGE('Submission KPIs'[Trust] )),
CONCATENATEX(
TOPN(
3
,'Submission KPIs'
,[AvgRankByModel]
,DESC
)
,'Submission KPIs'[Model_Name]&"("&FORMAT([AvgRankByModel],"##%")&")"
,", "
,[AvgRankByModel]
))
Hi @Anonymous,
Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.
If these also don't help, please share more detailed information to help us clarify your scenario to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
@Anonymous That code is returning a table and measures have to return scalar values. Try this:
TOP3Model1 =
VAR __Table = SUMMARIZECOLUMNS('Submission KPIs'[Cluster_long], 'Submission KPIs'[Model_Name], "AvgRankByModel", AVERAGE('Submission KPIs'[Trust] )
RETURN
CONCATENATEX(
TOPN(
3
,__Table
,[AvgRankByModel]
,DESC
)
,'Submission KPIs'[Model_Name]&"("&FORMAT([AvgRankByModel],"##%")&")"
,", "
,[AvgRankByModel]
))
Thanks @Greg_Deckler , i made a change to it and it worked. Can you also help me how can i reverse the order of the result. Current Ouptut is ETS(40%) ,Arima(50%), Prophet(60%), i think its because of the FORMAT String. How can i make it output display as Prophet(60%), Arima(50%), ETS(40%)?
Hi @Anonymous,
Perhaps you can try to modify the sort parameter DESC to ASC if it help with your requirement.
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |