Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!!
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.
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.
RANKX ( ALLSELECTED (Data[Source] ) , [Sales] )
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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.
It might be worth showing a screenshot of your data model just in case something in the relationships is causing an issue.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Please see below.
The only relationship is the transaction date with the date table, so I don't think the cause lies here.
@kiwicam is it possible at all to post the sample pbix?
@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!
@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.
Unfortunately no.
I guess I will just keep looking for a solution.
Thank you anyway.
So, I was able to scrub the data and share a sample file.
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.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
73 | |
65 |