RANKX - Multiple Slicers - What am I doing wrong?

[kiwicam]

I am trying to use RANKX together with multiple slicers for Month ('Dates'[Month]) and Source ('Data'[Source]) but getting an incorrect result.

Rank = RANKX ( ALL (Data[Source] ) , [Sales] )

The unfiltered table (top) works fine (see below), however, when I apply a Source slicer (bottom), some of the results get messed up (in blue rectangles).

If I apply a Month slicer, the results for all Months are fine (see the example below).

However, if I apply Month and Source slicers, the results are again messed up - and even results that were correct with just a Source slicer are now incorrect (see the example below).

So it seems like the Source slicer is the problem, but I have no idea why / how to fix it!

It feels like I am missing something extremely basic, but can someone please tell me what I am doing wrong / how to fix it?

Your help is greatly appreciated!!

[v-eqin-msft]

Hi @kiwicam ,

I have built a data sample based on your screenshots:

And I used your [Rank] measure, it seems that the two slicer worked on my side:

 

The only difference may be the [Sales] Measure, I just simply used the SUM('Data'[Value]) ,please try again.

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

[kiwicam]

Hi @v-eqin-msft

Thank you for your reply.

Yes, I am using a simple sum for the 'Sales' measure.

Sales = SUM(Data[Value])

I believe you are using random data with at most 1 line per source for each month.

Indeed, when I use 1 line of data per source for each month, there is no problem with the ranking.

So I guess the problem is happening when RANKX is iterating through the 100,000+ lines of data that each source has per month.

[CNENFRNL]

RANKX ( ALLSELECTED (Data[Source] ) , [Sales] )

[kiwicam]

Unfortunatetly no as that will limit the group to only 1 source thus always creating a rank of 1.

The rank analysis needs to be over all sources, and then just display the results for the selected source/month.

[KNP]

It might be worth showing a screenshot of your data model just in case something in the relationships is causing an issue.

[kiwicam]

Please see below.

The only relationship is the transaction date with the date table, so I don't think the cause lies here.

[smpa01]

@kiwicam  is it possible at all to post the sample pbix?

[kiwicam]

@smpa01 Thank you for looking at this.

Unfortunately I cannot share the pbix as it contains sensitive data. Additionally, the full file contains 44 million lines of data and even if I was to delete data before 2021, it would still be 8 million lines of data, so it is huge.

Sorry!

Any help would be greatly appreciated!

[smpa01]

@kiwicam is it possible to produce a sample pbix in a very small scale (by getting rid of the sensitive issue) that is representative of the issue. RANKX is a complex function and it would be definitely intetesting to get a chance to take a look at a problematic RANKX. 

[kiwicam]

Unfortunately no.

I guess I will just keep looking for a solution.

Thank you anyway.

[kiwicam]

So, I was able to scrub the data and share a sample file.

20211129_Sample.pbix (14MB) 

However, it is really weird; every time I open the file it seems a different source does not match up.

In the screen shot below SCR 01 is incorrect when filtered, but earlier today I saw SCR 03, SCR 07 and SCR 14 incorrect at different times of the day.