topic Calculated column for rank/index in DAX Commands and Tips

Calculated column for rank/index

Kazu — Tue, 19 Mar 2024 14:53:14 GMT

I want to add an index or rank column like below, based on the values of other columns. I know it is possible in Power Query but would like to do it in DAX if possible. I guess this is doable using RANK() function if it is a measure, but RANK seems not available for calculated column. Are there any way to do this for a column? I need this information for an axis, not a value.

Re: Calculated column for rank/index

tackytechtom — Tue, 19 Mar 2024 15:12:10 GMT

Hi Kazu ,

How about this?

Here a possible solution in DAX:

Column = 
RANKX ( 
    FILTER ( 
        'Table', 
        AND ( 'Table'[Category1] = EARLIER ( 'Table'[Category1] ), 'Table'[Category2] = EARLIER ( 'Table'[Category2] ) )
    ),
    'Table'[Element],
    , ASC
    , DENSE
)

I took the code snippet from here:

https://www.tackytech.blog/how-to-crack-the-mystery-of-the-mighty-dax/#create-ranks-and-indexes-with-calculated-columns

Also, it is not best practice to use earlier, so here a solution with the same outcome, but with the usage of variables instead:

Column = 
VAR CurrentCategory1 = 'Table'[Category1]
VAR CurrentCategory2 = 'Table'[Category2]
VAR MatchingRows =
    FILTER (
        'Table',
        'Table'[Category1] = CurrentCategory1 &&
        'Table'[Category2] = CurrentCategory2
    )
RETURN
    RANKX (
        MatchingRows,
        'Table'[Element],
        ,
        ASC,
        DENSE
    )

However, you could also solve it in Power Query. This tends to be the preferred solution as per Roche's maxim of data transformation. I'll post also a solution in Power Query below 🙂

Let me know if this helps!

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

Re: Calculated column for rank/index

tackytechtom — Tue, 19 Mar 2024 16:40:52 GMT

Hi Kazu ,

As promised, here the solution in Power Query:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlFwVNJRAuEIIDY0UIrVQUg5IUkZoUo5Q6UigdgURcYFSQbNPFdkKVRdblCpKCA2RpFxB4o4QR2BqscDKoPFJk/cUl5IUkaoBnpDpaIwrPJBkgGFRCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Element = _t, Category1 = _t, Category2 = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Element", type text}, {"Category1", type text}, {"Category2", type text}, {"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Category1", "Category2"}, {{"Grouping", each _, type table [Element=nullable text, Category1=nullable text, Category2=nullable text, Value=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn ( [Grouping], "Index", 1 )),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Element", "Category1", "Category2", "Value", "Index"}, {"Element", "Category1", "Category2", "Value", "Index"})
in
    #"Expanded Custom"

This one, I took from here:

https://www.tackytech.blog/how-to-swiftly-take-over-power-query/#create-ranks-and-indexes

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

Re: Calculated column for rank/index

Kazu — Tue, 19 Mar 2024 16:35:08 GMT

This is EXCELLENT. Thank you. When I trided before, I could not choose RANK or RANKX in my formula for some reasons and thought perhaps they were only available for measures. I can see them now. Interesting... 🙄

I know the "as far upstream as possible, and as far downstream as necessary" principle. In my case the only reason why I would like it to be a DAX is because I use a shared dataset. I can add mesures in my reports, and even clomums if I add a local model on top of the dataset, but I don't think I can apply additional transformation like Table.Group(). Maybe I am wrong? The index/rank column is a very specific need for my report and not as reusable as should be added to the upstream shared dataset.