Rankx calculated column unexpected ranking discrepancies

[Joris_NL]

Hi,

I have a perfectly working measure to give each 'Location' a different value. None of these values are unique, to prevent multiple locations ranked on the same position (because for example X = 2nd  and Y = 2nd would be a problem for visuals).

Based on this 'Location value', I've added a calculated column (for use in filters/slicers) to rank the locations with RANKX.

The ranking only applies to a selected 'Species group'. So for birds X = 1 and Y = 2, but for mammals Y = 1 and Z = 2, for example. 

For 307 locations, this works just fine. But weirdly enough it goes wrong for 3 locations (marked in yellow), which have very low location values (also marked yellow), but are somehow matched equally to positions 1, 3 and 4. This makes no sense to me.

What am I doing wrong?

location rank srtgrp = 
VAR SelectedUser = Data[user]

VAR SelectedSpeciesGroup = Data[species group]

VAR TabelUser = 
    FILTER ( Data ; 
    Data[user] = SelectedUser )

RETURN
RANKX (  
    TabelUser  ; 
    CALCULATE ( 
        [Location value] ; 
        ALLEXCEPT( Data ; Data[location] ) ; 
        Data[species group] = SelectedSpeciesGroup 
    ) ;; DESC ; Dense
)

[v-tianyich-msft · Accepted solution]

Hi @Joris_NL ,

I can give you the idea of converting it to a measure, realized through a virtual table, as you can see in the following results:

Measure = var _t = ADDCOLUMNS('Table',"Rank",RANKX(FILTER(ALL('Table'),[ColumnX]=EARLIER([ColumnX])),[Value],,DESC,Dense))
RETURN MAXX(_t,[Rank])

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Joris_NL · Accepted solution]

Thank you. The example file worked, but integrating the measure into my file (with more than those columns to filter on) went wrong.

I was actually hoping for a column as a solution, so I would be able to use it as a slicer/filter. So I delved deeper and (finally!) found a rare occurance of two different products (animal species in my case) with the same name.  Ranking 310 locations gave only 3 discrepancies which hadn't been noticed in other measures. So nothing wrong with the DAX I posted here - and yours probably work fine as well. Again, thank you for the effort! I'll use your RANKX measure next time.

[v-tianyich-msft · Accepted solution]

Hi @Joris_NL ,

I can give you the idea of converting it to a measure, realized through a virtual table, as you can see in the following results:

Measure = var _t = ADDCOLUMNS('Table',"Rank",RANKX(FILTER(ALL('Table'),[ColumnX]=EARLIER([ColumnX])),[Value],,DESC,Dense))
RETURN MAXX(_t,[Rank])

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Joris_NL · Accepted solution]

Thank you. The example file worked, but integrating the measure into my file (with more than those columns to filter on) went wrong.

I was actually hoping for a column as a solution, so I would be able to use it as a slicer/filter. So I delved deeper and (finally!) found a rare occurance of two different products (animal species in my case) with the same name.  Ranking 310 locations gave only 3 discrepancies which hadn't been noticed in other measures. So nothing wrong with the DAX I posted here - and yours probably work fine as well. Again, thank you for the effort! I'll use your RANKX measure next time.

[lbendlin]

What am I doing wrong?

You created a calculated column from a measure. That is unfortunately technically possible but it makes no business sense, as you are missing the filter context.

[Joris_NL]

Thanks for the tip, but people doing this wrong would need a bit more explanation than this. If I understand correctly, solve it either by ranking with (calculated) columns only, or by measures only. But not with measures inside calculated columns. Right?

In my case, that would either require very elaborate calculated columns with lots of variables or lots of columns. Or I could solve it by using measures only, not allowing me to filter or slice on visuals for let's say 'location ranked 3rd' only.

[lbendlin · Accepted solution]

Here's a great article: https://www.sqlbi.com/articles/use-of-rankx-in-power-bi-measures/