Filter Column in Measure AND Slice at the same time

[anmattos]

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.

Each column is from a Measure that measures this specific status, like that:

=CALCULATE (

    [Total Requirements];

    fWDC[Plan Status] = "Approved")

The Column with the status in the data is “fWDC[Plan Status]”.

The problem is that the slicer won’t work on this table, because the same column is used both in the measure and in the Slicer. In this case the measure overrides the status selection in the visual Slicer.

What I need is that when a Status is selected in the Slicer, the columns related to the other status show “0”. This is needed because there is a chart connected to the table.

Example: if I select "Released" in the Slicer, all columns will show 0 except the "Released" column, which will show its current numbers. The same should work if I select multiple values from the slicer.

Basically I need an “AND” condition between the [Plan Status] value in the measure formula and the slicer selection(s).

Is there any way to achieve that?

Best regards,

[jgeddes · Accepted solution]

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]

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