CODEWITHSUNDEEP
excelpivot-tablepowerpivot
MMerry
MMerry

Reputation: 334

Selecting a measures value with slicers in PowerPivot

I'm using PowerPivot in Excel 2010 to analyse some data. I have a situation where I have several boolean measures evaluating whether or not a condition is met. My Pivot table looks like this:

ROW LABEL    VALUE     CONDITION_MET
-----------------------------------
AAAA         10.5      TRUE
AAAB         9.5       FALSE
AAAC         11.29     TRUE

The measure to determine whether or not the criteria is met is non-trivial so I can't get users to select the right combination of groups in slicers to find the data. Ideally I want a solution that allows me to have a slicer that looks like below:

SLICER
-----
TRUE
FALSE

This filters my pivot table accordingly to just the records that match. Anyone got any ideas? I'm reading about disconnected slicers but not making much progress.

Upvotes: 0

Views: 1623

Answers (1)

Jacob
Jacob

Reputation: 3557

Disconnected slicers is one way to tackle this problem.

Lets say you create a 2 row table called 'TrueFalse' with a column called 'tf' and rows TRUE and FALSE.

You can then write a measure that counts the number of rows in that table that equal the value of the measure - if you then slice on the 'TrueFalse' table the option that you haven't selected will return BLANK() so will not be displayed.

The measure could look something like this:

=CALCULATE (
    COUNTROWS ( truefalse ),
    FILTER ( truefalse, truefalse[TF] = [Measure] )
            )

Upvotes: 1

Related Questions