Limiting the Selection of a Slicer Value to ONLY One Selection?

[CSMcVey]

I have a PP workbook loaded into SP and it has a slicer for 'state'. For security purposes we would like to limit the data displayed in the table to only show data for one state at a time.

Is there a way to limit the slicer to have only one value selected at a time?

If not, does anybody know of a method where I can accomplish the same objective? Meaning a user would have to select only one value in the slicer, if they selected multiple the data wouldn't show any data or, at the very least, just one state.

 

[MD610]

You can accomplish this by wrapping every measure displayed in your pivot with a HASONEVALUE() test on the state slicer column.

New Measure:=IF(HASONEVALUE(TableName[State]), [Original Measure], BLANK())

This must be done for every measure in the pivot. Then they will be blank if more than one value is selected in the state slicer.

 

[CSMcVey]

Thank you MD - just read up on this function and it will do exactly what we need. However, we are still using the old PowerPivot version (2008) and this function was an enhancement in a more recent build. Getting our environment up to speed is out of my control as I have tried before.

Any ideas on how to accomplish the same thing as HASONEVALUE like suggested above?

THANKS again for the assistance

 

[MD610]

You can substitute COUNTROWS(VALUES(TableName[State]))=1 for HASONEVALUE(). It will do the same thing.

New Measure:=IF(COUNTROWS(VALUES(TableName[State]))=1, [Original Measure], BLANK())