Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
kiwicam
Helper I
Helper I

RANKX - Multiple Slicers - What am I doing wrong?

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).

source.png

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

month.png

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).

month_and_source.png

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!!

11 REPLIES 11
v-eqin-msft
Community Support
Community Support

Hi @kiwicam ,

 

I have built a data sample based on your screenshots:

Eyelyn9_2-1637648361814.png

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

 Eyelyn9_3-1637648400527.png

 

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.

CNENFRNL
Community Champion
Community Champion

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.

 

20211124 allselected.png

KNP
Super User
Super User

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 ;).
chrome-9xf-Zagzel-B

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.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Please see below.

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

20211124 relationship.png

smpa01
Super User
Super User

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@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. 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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.

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.

 

20211129_Incorrect_Rank.png

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.