Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
For Example, consider the below "Tickets" table which contains different categories and subcategories.
I am trying to show the top 2 categories in the bar chart and drill down by one of the top 2 categories, it should show the top 2 subcategories with the selected category. Please refer to the table and bar visual "Top 2 Category & Sub Category by Tickets" which should get the top 2 categories as well as the top 2 subcategories within the selected category.
As of now, it's not giving the expected output.
DAX :
----
Attaching the sample pbix for reference.
https://drive.google.com/file/d/1nm0fTtVSfmMDX1vZ4yG04VmsAWczyNcN/view?usp=sharing
you're pretty close, but you need to use nested RANKX, not alternating RANKX if you want to show the top two subcategories inside each of the top two categories.
Could you help me by sending the the DAX for Nested RANKX with the pbix shared?
Here is one implementation
CR := RANKX(ALL(Tickets[category]),CALCULATE(sum(Tickets[number]),all(Tickets[subcategory])))
SCR := RANKX(Filter(ALL(Tickets[category],Tickets[subcategory]),Tickets[category]=max(Tickets[category])),CALCULATE(sum(Tickets[number])))
This will pull the first two categories, and then the first two subcategories for these categories (if you set filters for CR<3 and SCR<3)
Also refer to this explanation: https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures