Forum Discussion
DAX help
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
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:
- 28 this would equal ignore everything
- 14 the sum of all quantities of category2, Q (2 + 9) and S (3)
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,
- Yes, when I select category1 “A” answer should be 14
- Function is a DAX measure
- If I select category1 = A and category2 = S answer should still be 14 (as I don't want any kind of filtering on my "category2" column)
- we can say category1 and category2 are independent.
Most importantly,
when i filter date From "01st Jan 2018 to 10th Jan 2018"
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