Calculated numeric field based on text field

[Jackofall]

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.

[AlB · Accepted 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]  

[AlB · Accepted 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]  

[Jackofall]

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.

[AlB · Accepted solution]

@Jackofall

In fact what you are doing in M is add a column