Forum Discussion

dilumd's avatar
dilumd
Impactful Individual
7 years ago
Solved

DAX help

Hi All,

 

I have below simple table,

DateCategory1Category2Qty
1/1/2018AQ2
1/2/2018AS3
1/3/2018BR4
1/4/2018BT6
1/5/2018CY4
2/1/2018AQ9

 

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

Without any filterwhen i click on category2 "Quantity_M" doesn't change (which is what i want)when i apply a filter on datesafter the date filter when i click on "category2" (above behavior changes and "allexcept" function doesn't apply after that.

Can some help me to understand this and tell me how to get it right please.

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

10 Replies

  • 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