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
I'm not sure why your function dosn't act as expected, but is it important to you, that it is a DAX formula that makes the qty persist the filter?
Instead you could change the interaction, with the qty and catagory 2.
You can do this by clicking on the slicer "Catagory 2",
Then choose Format and Edit intereactions.
Then change the intereaction on the qty.
Then this field will no longer be affected by the choices of the catagory 2 slicer.
Hi @Thim
Thanks for your reply! But I need this behavior for my other calculations, edit interactions doesn’t serve the purpose for me.
This is how I would expect the results to be
because you are removing the filters from Date and Category1
Your DAX is similar to
Quantity_M = CALCULATE ( SUM ( Test_Table[Qty] ), ALL ( Test_Table[Category2] ) )
You can use ALL(Test_Table) if you want a static result of 28.
The date slicer would filter the dates in the Table but the MEASURE won't change
Hi @Zubair_Muhammad,
Quantity_M = CALCULATE ( SUM ( Test_Table[Qty] ), ALL ( Test_Table[Category2] ) )
This function works very similar to my previous all except function. But whenever I add a filter on date that behavior breaks and then it’s getting filtered on category2 as well.
As far as I’m not adding any filter on dates this works but as soon as filter comes on date whole thing breaks and start filtering on category2.
Hope I explain you this now.
Thanks a lot.
I get your point.
I think the only way is to use Diable interaction between visuals because slicer selection will filter the rows of other visuals irrespective of the MEASURE we write.
But may be i am missing something.
Could you help
Hey @dilumd,
I have to admit that I do not understand what you want to achieve.
As no filter is applied your function returns a total of 28
This is also true when you apply a filter to categor2, this is because of ALLEXCEPT(...).
Now, I'm wondering what you expect if you select something from the slicer category1 in my example I choose A.
This filters down the rows to this
I'm wondering what your function, please let's call it a measure - I assume you used the DAX statement to create a measure - should return.
I can imagine these values:
What is your expected result if I choose:
category1 = A and
category2 = S
Are category1 and category2 in a way independent, meaning something like product and country. And could it be possible that each product could be sold in each country even if this is not reflected by the data.
Please provide a simple example what value you expect for some filter settings.
Regards,
Tom
Hi @TomMartens,
Thank you in advance.
Answering to your questions,
Most importantly,
When I filter date from 01st Jan 2018 to 10th Jan 2018 answer is "19" which is fine, But when I click on cateory2 filter (e.g. "R" in below case) while the date filter is as it is ("01st Jan 2018 to 10th Jan 2018") answer "19" should not be changed, I need Quantity_M as "19" not "4".
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
Hi @Zubair_Muhammad,
Thanks for the reply. When i click date and the category1 slicers value should change, but even if i click category2 values should not change.
That's my requrement.
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |