Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Kazu
Helper II
Helper II

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.

 

Kazu_0-1710859842767.png

 

1 ACCEPTED SOLUTION
tackytechtom
Super User
Super User

Hi @Kazu ,

 

How about this?

tackytechtom_0-1710860725908.png

 

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

 

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

View solution in original post

3 REPLIES 3
tackytechtom
Super User
Super User

Hi @Kazu ,

 

As promised, here the solution in Power Query:

tackytechtom_1-1710861446541.png

 

 

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/

 

 



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

tackytechtom
Super User
Super User

Hi @Kazu ,

 

How about this?

tackytechtom_0-1710860725908.png

 

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

 

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors