Forum Discussion

Kazu's avatar
Kazu
Icon for Helper II rankHelper II
2 years ago
Solved

Calculated column for rank/index

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.

 

 

3 Replies

  • tackytechtom's avatar
    tackytechtom
    Icon for Most Valuable Professional rankMost Valuable Professional
    2 years ago

    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/

    • Kazu's avatar
      Kazu
      Icon for Helper II rankHelper II
      2 years ago

      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.

  • tackytechtom's avatar
    tackytechtom
    Icon for Most Valuable Professional rankMost Valuable Professional
    2 years ago

    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/