Calculated numeric field based on text field

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.