Forum Discussion

dilumd's avatar
dilumd
Impactful Individual
7 years ago
Solved

DAX help

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 ...
Need Help
  • TomMartens's avatar
    TomMartens
    7 years ago

    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

Thim's avatar
Thim
Resolver V
7 years ago

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.