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.
I have a hard time understanding the prereqs of DAX.
I am playing around with a toy database to learn more about PBI. I have a table that cointains a text column named Rank. The values there are deliberately contradicting sortorder. S is highest then comes A etc. So in order to get the sorting I want I would usually use IF(Rank = 'S', 1, IF(Rank = 'B', 2, ...) in T-SQL or Report Builder to get the sort order I want.
When I try this in DAX I get an error message that there is no aggregate of the textcolumn. "A single value.... without specifying an aggregation such as min, max,count or sum to get a single result".
I found a much earlier Message here in the From about this problem and saw that it was better to use M in the query Editor.
if Text.Contains([Rank], "A") then "a" else "B"
I could get that to work as far as syntax goes but when I try to apply the query changes it takes forever to update 150 rows of data. I thought it bombed but it came through after 5ish minutes. When I looked at the code after apply went through I had.
= Table.AddColumn(dbo_Draft_ranking, "SortOrder", each if Text.Contains([Rank], "S") then "1" else if Text.Contains([Rank], "A") then "2" else if Text.Contains([Rank], "B") then "3" else if Text.Contains([Rank], "C") then "3" else "4")
I checked my SQL-DB there was no new column created as I feared for a while.
How should I aggregate a text column to get the measure to do the same thing as I managed in the modelling of the query? If Rank = "S", 1, 0 is simple enough but DAX gives me headache.
Solved! Go to Solution.
Hi @Jackofall
I think you want to do that in a calculated column rather than in a measure. There you will not have the problems of aggregation. In a measure you do not have a row context, that is why DAX does not know what you are referring to when you write Draft_Ranking[Rank]
Hi @Jackofall
I think you want to do that in a calculated column rather than in a measure. There you will not have the problems of aggregation. In a measure you do not have a row context, that is why DAX does not know what you are referring to when you write Draft_Ranking[Rank]
Thanks for the feedback.
When I changed the Default Summarization I could sord Rank by SortOrder.
What I would do in T-SQL is to sum(case when d.Rank = 'S' then 1 else 0 end) as 'S' so I understand where my reasoning was off. There is the aggregation Another way I force sort order is to use union and set different values '3' as [Sortorder] then sort on that column in Report Builder. I managed to fool my self totaly. Thanks for straightening me out.
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |