Forum Discussion

Paramjit's avatar
Paramjit
Icon for Helper II rankHelper II
5 years ago
Solved

DAX Measure All Except is not working as expected

Date Category Specific Filter 01-May-21 A Great than 40 02-May-21 A Great than 40 03-May-21 A Great than 40 04-May-21 A Great than 40 05-May-21 A Great than 40 06-May-21 A Great than 40 07-May-21 A ...
Need Help
  • SivaMani's avatar
    SivaMani
    5 years ago

    Paramjit,

     

    Try the below steps,

    1. Create a table for Specific Filter (It will only have one column with 2 values)
    2. Join the new table and existing table using Specific Filter
    3. Changes the measure
    TOTAL COUNT = CALCULATE([Count],ALL('Specific Filter'[Specific Filter]))

     

    It will work.

Paramjit's avatar
Paramjit
Icon for Helper II rankHelper II
5 years ago

Hi Siva,

 

Let me try to explain again. 

 

we just need  (rows count) divied by (total rows count) .  

 

'rows count' is the count that came after applying all slicers.

 

'total rows count' is the count that came after applying all slicers except one slicer (in this case 'specifc filter') .  

 

Please see 3rd and 4th screenshot. ( Total count should be 20 as B and C values are slected in Category slicer. total count should not impected by 'Specific filter' )

 

Thank you,

Paramjit Singla

SivaMani's avatar
SivaMani
Icon for Resident Rockstar rankResident Rockstar
5 years ago

Paramjit,

 

Try the below steps,

  1. Create a table for Specific Filter (It will only have one column with 2 values)
  2. Join the new table and existing table using Specific Filter
  3. Changes the measure
TOTAL COUNT = CALCULATE([Count],ALL('Specific Filter'[Specific Filter]))

 

It will work.

  • Paramjit's avatar
    Paramjit
    Icon for Helper II rankHelper II
    5 years ago

    Thank you SivaMani  : ) and Congrats, the above solution worked well.

     

    just two questions: 1. should not it work with the way i am doing? All Except should ignore the filter selection but it is not doing. is it a bug ?

    2.  can creating a virtual table create performance issue? 

     

    Thank you,

    Paramjit

     

    Thank you,

    Paramjit singla

    • SivaMani's avatar
      SivaMani
      Icon for Resident Rockstar rankResident Rockstar
      5 years ago

      Paramjit, I am glad that it worked.

       

      #1, It depends on the cases.

      #2,  If you created in Power Query, mostly no harm. It is a best practice to have dimension tables

      • Paramjit's avatar
        Paramjit
        Icon for Helper II rankHelper II
        5 years ago

        Thank you Siva for all your help...