Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
// Note: [Master Product] is not the key level of Product but a higher level "Parent" product // Countrows works perfectly and retains the same Total of Category value even when used within additional Product filters measure 'Sales'[MyCount] = var tbProds = calculatetable(filter(all('Product'[Master Product]),[Sales]>0), ALLEXCEPT('Product','Product'[Category])) return countrows(tbProds) // Rankx only works as long as no additional filters are applied // When additional Product filters are applied (e.g. Product[Vendor]) it ranks [Master Product] within that filter not within Total Category // Why is one function affected by filters the other not? // How do we achieve a ranking within Category that is independent of filters? measure 'Sales'[MyRank] = var tbProds = calculatetable(filter(all('Product'[Master Product]),[Sales]>0), ALLEXCEPT('Product','Product'[Category])) return rankx(tbProds,[Sales],,,dense)
The above snippet shows 2 measures, MyCount works as expected, MyRank does not. Hopefully the comments tell most of the story. The goal is to get a rank over Product[Category] that is constant regardless of any other Product filters. The VARs are for readability only, we get the same results without using VARs. Any ideas how to achieve this?
@Anonymous ,
You may try ALLSELECTED.
https://community.powerbi.com/t5/Desktop/RANK-MIN-OF-SUM-grouped-by-two-fields/m-p/581289#M275025
ALLSELECTED has the opposite effect to what I require. From https://docs.microsoft.com/en-us/dax/allselected-function-dax
"The ALLSELECTED function gets the context that represents all rows and columns in the query, while keeping explicit filters and contexts other than row and column filters."
I want to remove all filters except the current Product[Category] which should be what ALL() does.
I suspect this is something to do with RANKX being iterative but I dont understand it enough to explain why. I have even tried the countrows(filter(table,sales>currsales variable)) technique and that doesn't work either. Probably because of filter iterating too.
Thanks for replying though
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
82 | |
70 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |