skip to Main Content

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

 
It’s Actually VERY Simple to Catch Users’ Slicer Selections in Excel Formulas, IF You Are Using PowerPivot (AKA the 2013 Data Model Feature)

It’s Actually VERY Simple to Catch Users’ Slicer Selections in Excel Formulas, IF You Are Using PowerPivot (AKA the 2013 Data Model Feature)

It’s Actually VERY Simple to Catch Users’ Slicer Selections in Excel Formulas,
IF You Are Using PowerPivot (AKA the 2013 Data Model Feature)

A Popular Topic

No, I don’t mean things like “Fox Urine” or “Face to Anogenitaled” – those are pretty funny of course, and they come up in my job because I consult for my scientist neighbor on his lab rat projects.

But no, I’m here to talk about something even more popular than Fox Urine Smile

Every day, one of the most-read topics on this blog is some variant of “I want to catch slicer selections in formulas.”  This has been covered in at least three different posts:

  1. https://powerpivotpro.com/2010/06/use-slicer-values-in-a-calculation-with-powerpivot-dax/
  2. https://powerpivotpro.com/2010/12/another-way-to-get-and-use-slicer-values-in-formulas/ 
  3. https://powerpivotpro.com/2011/09/catching-multiple-slicer-selections-in-a-formula/

There have also been a number of comments on each post that suggested alternate (and often better) ways of doing things.  I’ve never been terribly comfortable with that third post in particular, the one dealing with multiple selections.

Funny thing is, I used a new technique (for me) in the Calendar Chart posts, but never went back and called it out explicitly.  Today I am going to correct that omission.

Read the Rest

Conditional Formatting via Slicers, Part Two

 
Using PowerPivot Measures and Slicers to Control Conditional Formatting on the Pivot!

Each Number Refedit in the Edit Rule Dialog Points to a Different Cell on the Sheet

Correcting for that bug

In the last post on this topic, I discovered what I think is a bug in Excel 2010 conditional formatting, one that prevented me from using the Percentile threshold type with a cell reference. 

But the Number type works great, so if I can get the actual [Profit] value for, say, the 80th percentile Model Name into a cell, I can reference that.

And that’s precisely what those three cells in the image above contain:

image

Cell F2 Contains a Cube Formula that Returns the Measure [Profit Required for Green CF]

OK, so how do I calculate that [Profit Required for Green CF] measure?

Read the Rest

Conditional Formatting Controlled via Slicers!

 
Yes folks, we’ve seen all manner of parameterization by slicer.  We’ve even seen sort by slicer.  But now it’s time for me to track down a hunch I’ve had for awhile now:  we can also control conditional formatting via slicers!

image
At the 65th Percentile, Model Name Profits are Being Shaded Green

image

Getting Stricter:  Use the Slicer to Set the “Green Threshold” to 95th Percentile

Read the Rest