Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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] )
)
)
Proud to be a Super User!
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] )
)
Proud to be a Super User!
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] )
)
)
Proud to be a Super User!
User | Count |
---|---|
98 | |
91 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |