RANKX fucntion returning false ties

[calleylmurphy]

Hello,

I'm trying to use the RANKX function for the fist time, and am getting ranks that show up as ties when they are clearly different values.  I'm using this formula:

Rank = RANKX(ALLSELECTED('Test Table'),[TestValus],,DESC,Skip)

...And here's what I'm looking at as a result:

TestValues   Rank

8,528.130     1

7,880,684     2

5,658,992     3

5,501,675     4

4,035,689     5

5,345,703     5

3,959,043     5

3,929,863     5

4,496,415     5

3,431,168     7

In general it seems to be working, but I can't figure out why I would be getting random ties for values that are clearly not the same.  Does anyone know why this might be happening?

[sebastiaan-pbi]

Hi, did you ever find out what what causing the false ties?

I am experiencing the same behaviour when ranking on currency values.

The formula I goes something like this:

Rank Revenue := RANKX ( ALL ( 'TABLE'[Dimension] ); [Revenue])

When applied on Revenue (currency), it results in random(!?!) false ties. Each refresh gives different results.

When applied on Volume (whole number), it behaves as expected...

Cheers, Sebastiaan

[v-shex-msft]

Hi @sebastiaan-pbi,

First, ALL function will break current filter effect, so I'd like to suggest you to use ALLSELECTED to instead. You can try to remove the column name, if you add column name to ALL function, it will only check the filter on the specific column.

Please try below formula if it works on your situation:

Rank Revenue := RANKX ( ALLSELECTED ( 'TABLE' ); [Revenue])

BTW, you can also check table relationships if 'cross filter direction' option not setting to both.('single' only works on one side, for other side, it equal to not have filter)

Regards,

Xiaoxin Sheng

[sebastiaan-pbi]

Hi Xiaoxin,

Thanks for your response! I will try out the ALLSELECTED function and compare it to ALL.

As for the ranking issue, I am pretty sure there's is a bug in DAX/Power BI when ranking on a currency column. And it is not a rounding issue -> the bug produces random ties. That is, after refreshing the data in Power BI, the ranks update and produce false ties on different positions each time. And the dataset does not change for sure. It's really strange.

We worked around it by converting the decimal to int. I have tried to reproduce the issue using a simplified anonimized dataset in Excel, so I can share it on this forum. However, the issue does not usually show up, otherwise it would have been fixed by now, and I cannot share our data unfortunately...

If I have time later on, I'll retry to reproduce the problem.

Thanks again,

Sebastiaan

[Anonymous]

Converting my measure Format to Whole Number seemed to fix the issue. I've spent hours trying to figure this out.

I need a better understanding of DAX overall, but this does seem like a bug on currencies.

[Anonymous]

What is the expression "Test Values"?  Is it really just a single unique value, or is it a Measure/Formula that is summing up other values? 

[calleylmurphy]

Test values is a measure that's summing up other values.  I think I may have found a solution to my problem though...  But it relies on some guesswork of what's going on with the RANKX function:

Essentially, my understanding (guess) is that the rank is tied to unique rows of whatever table I put the measure on.  So when I create a table visual, the measure of "Test Values" shows up correctly as a sum of values for each category in my table visual; however, the rank does not update based on that measure value.  The rank seems to rely on the value of my measure as evaluated for each row of the table the rank was created on, and thus is dynamic in the context of a filter (using ALLSELECTED) but not in the context of a visual.

I think I've gotten it to work by creating a table with unique values for the category I want to display in my visual, then creating the rank in that table, so that it relies on the value of my measure "Test Values" as evaluated in each row of that new table.

Not sure if all of this makes any sense, but this is my current thinking and work-around!

[calleylmurphy]

Adding on to my previous post - still getting a few incorrect ties, even with the "create new table" work-around...

[v-shex-msft]

Hi @calleylmurphy,

Your formula works well on my side, can you share me a sample data to test?
In addition, you can try to use below measure to see if it works on your side:

Rank = COUNTROWS(FILTER(ALLSELECTED('Table'),'Table'[Sum]>MAX('Table'[Sum])))+1

Regards,

Xiaoxin Sheng

[calleylmurphy]

Hello,

Thanks for your suggestion; I'd like to try it out, but I'm not sure what Table[Sum] is.  Would that be a column within the table that contains the values to be ranked (for each row of the table), as opposed to a measure (what I'm currently ranking with)?

Thanks,

Calley

[v-shex-msft]

Hi @calleylmurphy,

Yes, it is a value column which you used to rank.

Regards,

Xiaoxin Sheng

[calleylmurphy]

Hi,

If you make the values you want to rank a column in the table, how do you use filters to have a dynamic ranking?  Won't the values in the table column, and thus also the ranking, be fixed regardless of what filters are applied?

I'm sorry if I'm missing something here!  I'm new to Power BI and still figuring out the best times to use measures vs. columns, etc...

Thanks,

Calley

[v-shex-msft]

Hi @calleylmurphy,

>>If you make the values you want to rank a column in the table, how do you use filters to have a dynamic ranking?  Won't the values in the table column, and thus also the ranking, be fixed regardless of what filters are applied?

ALLSELECTED function will work at the records which you selected, for detail information you can refer to the msdn library:

ALLSELECTED Function (DAX)

Regards

Xiaoxin Sheng