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.
I have two slicers category & product.
Let's say the table looks something like this:
Customer | Category | Product | Revenue |
X | C1 | P1 | 10 |
X | C1 | P2 | 30 |
X | C2 | P3 | 50 |
I want to calculate calculate the remaining revenue based on slicer selection product.
So for example,for customer X, if I choose category as C1 and C2 in category slicer and I choose Product as P1 in product slicer then I should get something like this:
Customer | Total Revenue | Remaining Revenue |
X | 10 | 80 |
But I get something like this:
Customer | Total Revenue | Remaining Revenue |
X | 10 | 30 |
However, if I choose one product each from both categories, then the results are correct, so let's say I choose P1 and P3 in the product slicer, I get this:
Customer | Total Revenue | Remaining Revenue |
X | 60 | 30 |
However, if one product for both categories isn't included then the remaining doesn't work write.
The measures that I have written so far are which aren't giving me what I want are:
Total Revenue = CALCULATE(SUM(Table[Revenue]))
Remaining Revenue = CALCULATE([Total Revenue], ALL(Table[Product]))
Any help would be appreciated.
Solved! Go to Solution.
Hi @afaro ,
Since both categories and products come from the same table, the Auo-Exist mechanism is triggered so that invalid filter combinations are removed.
For example, with C1 and C2 selected for categories and P1 selected for products, the following combinations would normally result:
Category=C1 && Product=P1 ||
Category=C2 && Product=P1
However, due to the Auto-Exist mechanism, the combination category=C2 && product=P1 is removed because product P1 does not belong to category C2.
So, it is required that the fields of both slicers cannot come from the same table, and a dimension table needs to be extracted to be used as the slicer's fields, e.g. you can extract category as the dimension table.
Here are the measure expressions and results:
Demo - ALL or REMOVEFILTERS not working as expected.pbix
Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !
Thank you~
Hi @afaro ,
Since both categories and products come from the same table, the Auo-Exist mechanism is triggered so that invalid filter combinations are removed.
For example, with C1 and C2 selected for categories and P1 selected for products, the following combinations would normally result:
Category=C1 && Product=P1 ||
Category=C2 && Product=P1
However, due to the Auto-Exist mechanism, the combination category=C2 && product=P1 is removed because product P1 does not belong to category C2.
So, it is required that the fields of both slicers cannot come from the same table, and a dimension table needs to be extracted to be used as the slicer's fields, e.g. you can extract category as the dimension table.
Here are the measure expressions and results:
Demo - ALL or REMOVEFILTERS not working as expected.pbix
Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !
Thank you~
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
71 | |
36 | |
21 | |
19 | |
15 |
User | Count |
---|---|
125 | |
37 | |
29 | |
29 | |
24 |