Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi!
I have a list of products that have different categories. I managed to make a formula that rank top products per category ie. every category has the top #1 product sold. Now the issue is that when I add a Year slicer (two years 2016-2017) the rankx formula does not do what I'd like to. When I choose the category from a filter and select only year 2017, the rank values in the table repeat when I choose for example year 2017 only.
Here's the formula for the RANK measure: RANK = IF(ISBLANK('powerBIdata'[Sum of Netsales]);Blank();RANKX(ALL('powerBIdata'[Product]);[KPI];;DESC;Dense))
I'd like to have ranks in normal order that take account the periods I select. Note that the KPI values change correctly with filters
Currently this is what I get:
I would really appreciate if someone finds some time to help me solve this issue 🙂
Solved! Go to Solution.
I think I figured it out! Here's the solution:
RANK = IF(HASONEVALUE(powerBIdata[Category]);rankx(ALL(powerBIdata[Product]);[KPI]))
I have to say that DAX is a tricky thing, lots and lots to learn.
@TomMartens, Thanks for your attention!
Hey,
put your RANKX() formula inside a CALCULATE() like this CALCULATE(RANKX(...))
Hope this solves your issue
Thanks for the speedy reply, but it did not help. I changed my table to a Matrix, put on a view that also shows categories. What appears to be happening is that the Category itself is ranked aswell. So, obviously the KPI sum of all the categories is higher than any of the products'. So the solutions appears to be to exclude the categories from the Rank calculation. I wonder how to do that... 🙂
And this means that the issue is not with the time slicers, but how to exclude the categories from the calculation. What is perplexing is that how does the categories get ranked.
Hey,
can you please provide a downloadable file with sample data.
Regards
I think I figured it out! Here's the solution:
RANK = IF(HASONEVALUE(powerBIdata[Category]);rankx(ALL(powerBIdata[Product]);[KPI]))
I have to say that DAX is a tricky thing, lots and lots to learn.
@TomMartens, Thanks for your attention!
It is 🙂
Please mark your post as answer, so others may benefit from your solution.
Cheers
OK, What I've noticed is that this might be a Powerbi table issue.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
91 | |
87 | |
80 | |
69 | |
69 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |