topic Rankx calculated column unexpected ranking discrepancies in DAX Commands and Tips

Rankx calculated column unexpected ranking discrepancies

Joris_NL — Thu, 04 Jul 2024 10:57:45 GMT

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 )

Re: Rankx calculated column unexpected ranking discrepancies

lbendlin — Fri, 05 Jul 2024 00:43:33 GMT

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.

Re: Rankx calculated column unexpected ranking discrepancies

Anonymous — Fri, 05 Jul 2024 06:51:41 GMT

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.

Re: Rankx calculated column unexpected ranking discrepancies

Joris_NL — Mon, 08 Jul 2024 13:18:11 GMT

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.

Re: Rankx calculated column unexpected ranking discrepancies

Joris_NL — Mon, 08 Jul 2024 13:29:50 GMT

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.

Re: Rankx calculated column unexpected ranking discrepancies

lbendlin — Mon, 08 Jul 2024 14:18:36 GMT

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