Hi All,
I have below simple table,
| Date | Category1 | Category2 | Qty |
| 1/1/2018 | A | Q | 2 |
| 1/2/2018 | A | S | 3 |
| 1/3/2018 | B | R | 4 |
| 1/4/2018 | B | T | 6 |
| 1/5/2018 | C | Y | 4 |
| 2/1/2018 | A | Q | 9 |
I want to calculate total quantity column which doesn't change based on the category2 filter. I wrote a function as follows which returns the expected results. But when I apply filter to a date column this behavior doesn’t persist (check below screenshots).
Function,
Quantity_M = CALCULATE(SUM(Test_Table[Qty]),ALLEXCEPT(Test_Table,Test_Table[Date],Test_Table[Category1]))
Can some help me to understand this and tell me how to get it right please.
Hey @dilumd,
understand! With your current data model (just one table) this will become a nightmare, for this reason I recommend to adjust your datamodel to this
I set the Cross filter direction between table1 and "Category2", because category2 seems less important to me than category1, maybe I err. But this allows that the content of the slicer for "Category 2" based on the coresponding table, namely "Category 2" reacts to selections of the slicer Category 1
Category1 --> Table1 <--> Category2
I created the tables Category 1 and Category 2, using these simple DAX statemens, e.g. Category 1:
Category 1 = VALUES(Table1[Category1])
Then I created a measure like so:
Quantity_T =
CALCULATE(
SUM(Table1[Qty])
,ALL('Category 2'[Category2])
)
This results to this report - please be aware that the content of the Category slicers is coming from the category tables:
Hopefully this is what you are looking for, or at least gives an idea, of course the new measure returns 19, if nothing from Category 1 is selected 🙂
Regards,
Tom
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
