Forum Discussion
RankX with Multiple Slicer
Hi everyone, hope you're doing well.
I have an issue regarding RANKX DAX while applying several slicers. Please kindly find the image below:
As you can see my DAX is not working properly. The rank is supposed to be flexible depends on the selected item on each slicer.
Here is my DAX:
And here is the data model:
Even though using the sample data, I try to create a similar data model like the original one. Do you have any idea what makes my DAX not working properly? Thank you for your help!
You can find the PBIX file here
RANK automatically considers the filter context from other fields such as Company (be sure to use Company Master[Company] in the visual). I added an IF statement to exclude non-existent combinations of Company/SKU:
Rank Sales = IF ( // rank only Company/SKU combinations that have sales NOT ISBLANK ( [Total Sales] ), RANK ( DENSE, ALLSELECTED ( 'SKU Master' ), ORDERBY ( [Total Sales], DESC, 'SKU Master'[SKU Code] ) ) )
4 Replies
Try this measure using RANK. It's a better choice than RANKX in most cases.
Rank Sales = RANK ( DENSE, ALLSELECTED ( 'SKU Master' ), ORDERBY ( [Total Sales], DESC, 'SKU Master'[SKU Code] ) )Hi DataInsights , Thank you for your help! your DAX works properly.
I have one more question, I've added a "company" field into my table. However, it turns out that the DAX is not considering rank between companies. Do you know how to fix it? Thank you!
RANK automatically considers the filter context from other fields such as Company (be sure to use Company Master[Company] in the visual). I added an IF statement to exclude non-existent combinations of Company/SKU:
Rank Sales = IF ( // rank only Company/SKU combinations that have sales NOT ISBLANK ( [Total Sales] ), RANK ( DENSE, ALLSELECTED ( 'SKU Master' ), ORDERBY ( [Total Sales], DESC, 'SKU Master'[SKU Code] ) ) )
Sorry for late reply, I have tried your DAX and it works! Thanks for your time and support
