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 R...
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.