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.

  • dilumd's avatar
    dilumd
    Impactful Individual
    7 years ago

    Hi Thim

     

    Thanks for your reply! But I need this behavior for my other calculations, edit interactions doesn’t serve the purpose for me.

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      7 years ago

      dilumd

       

      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

       

      • dilumd's avatar
        dilumd
        Impactful Individual
        7 years ago

        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.