Forum Discussion

anmattos's avatar
anmattos
Advocate I
3 years ago
Solved

Filter Column in Measure AND Slice at the same time

Dear All,   I have the following situation and I don’t seem to find anything similar on the forums. I´m using Excel PowerPivot.   I have a Pivot Table with some status on the Columns.   E...
Need Help
  • jgeddes's avatar
    jgeddes
    3 years ago

    You should be able to amend your measures to test if the [Plan Status] is filtered and if it is, return the selected value or 0 if the [Plan Status] is not selected or if it is not filtered return all values.

    Something like...

    Approved Value =
    SWITCH(
    TRUE(),
    NOT(ISFILTERED(fWDC[Plan Status])), CALCULATE([Total Requirements],fWDC[Plan Status] = "Approved"),
    AND(HASONEVALUE(fWDC[Plan Status]), FILTERS(fWDC[Plan Status]) = "Approved"), CALCULATE([Total Requirements],fWDC[Plan Status] = "Approved"), 
    0
    )     

     

jgeddes's avatar
jgeddes
Super User
3 years ago

You should be able to amend your measures to test if the [Plan Status] is filtered and if it is, return the selected value or 0 if the [Plan Status] is not selected or if it is not filtered return all values.

Something like...

Approved Value =
SWITCH(
TRUE(),
NOT(ISFILTERED(fWDC[Plan Status])), CALCULATE([Total Requirements],fWDC[Plan Status] = "Approved"),
AND(HASONEVALUE(fWDC[Plan Status]), FILTERS(fWDC[Plan Status]) = "Approved"), CALCULATE([Total Requirements],fWDC[Plan Status] = "Approved"), 
0
) 

 

  • anmattos's avatar
    anmattos
    Advocate I
    3 years ago

    Hello,

     

    Thank you for your suggestion. It was not exactly what I needed, since the second SWITCH criteria check for 1 filter selection only, while I wanted to be able to select multiple options in the visual Slicer.

     

    However, it nudged me in the right direction and in the end I got the desired result as follows:

     

    =
    VAR __Status =
              CALCULATE (
                   [Total Requirements];
                   fWDC2[Plan Status] = "Approved")
    RETURN

     

    SWITCH (
            TRUE ();
            NOT ( ISFILTERED ( fWDC2[Plan Status] ) ); __Status;
            CONTAINSROW ( FILTERS ( fWDC2[Plan Status] ); "Approved" ); __Status;
            0
    )

     

    Thank you