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
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