Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |