PowerPivot conditional formatting based on DAX slicer selection

[joeshu26]

Hello,

I have a "Measure" slicer that allows you to select either Nominal, YoY Growth %, or 5yr CAGR %. I then have a DAX measure that calculates based on what is selected in the "Measure" slicer:

Measure Type = IF([isReportSlicerSet],SWITCH(VALUES('Measure'[Measure]),"Nominal",[Total Revenue],"YoY Growth %",[YoY Revenue],"5yr CAGR %",[Revenue 5yr CAGR]))

What I need is for the out put for the formatting to be a decimal formatting for the "Nominal",[Total Revenue] selection and I need the output measure to be in percentage formatting for the YoY or 5yr CAGR selections.

Any ideas??
Thanks!

 

[ImkeF]

You can bring the selection of your slicer into a cell in your workbook which can then be referenced by the conditional formatting:

=CUBERANKEDMEMBER("ThisWorkbookDataModel";MeasureSlicer;1)

hth, Imke

 

[joeshu26]

Thanks Imke,

This is the formula I have now : =CUBERANKEDMEMBER("PowerPivot Data","[Measure]",1)

But it is returning "All", instead of "Nominal" or "YoY' when selected...

Any ideas on how to have it show up as just "Nominal" or just "YoY' when they are selected?

 

[ImkeF]

Yes, the 2nd element of this formular has to be the name of your slicer, not your measure. No "" and no [], just the plain text.

 

[joeshu26]

Awesome, thanks Imke! I also found this great reference for "Catching" data from slicers into excel so that slicer selections can be used in formulas or conditional formatting.

Better Way to “Catch” Multiple Slicer Selections in a Formula « PowerPivotPro

 

[ImkeF]

Pleasure - glad you also found The resource