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.
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.
Solved! Go to Solution.
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
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |