Dynamic Filtering with Power BI “What If” Parameters

With the recent introduction of “What If” parameters for Power BI, I thought I’d play with them a little bit and pondered whether they could be used to improve dynamic filtering of visuals. One thing I thought would be cool was if you could create a variable (a threshold, for example) and make your chart filter out everything underneath that. I could previously solve it with a bit of creativity but putting this into the hands of the user was a little out of my reach.

(Having now seen how the “What If” functionality works under the hood, I think that these limitations may have been more my ability to explore the DAX language feature set, but I digress…)

The Problem

For the purposes of illustration, I have some sales data and I want to be able to use a slicer to look at sales by different thresholds, so starting with everyone and then using a slicer to set a “sales over $N” threshold and having my visuals responding to that. Data is 1,000 rows, generated via mockaroo and looks like this:

Sample of test data generated for demonstration purposes

I’ll work through where I got to previously and then expand upon this to bring in the new functionality, but here’s the finished recipe:

Threshold filter parameter tied to slicer

Initial Exploration

Firstly I plot my sales by customer and I get a pretty busy chart:

That’s a lot of scrolling…

I can pop a filter in the visual to give me all customer with Total Sales ≥ 6 million and this looks a bit better:

Adding a filter to the visual

This is still static and very much depends on me, the author, deciding what the viewer might wish to see. My users might want to explore this a bit more with their own threshold.

Adding a Filter Measure

Currently the filters don’t support the use of variables, but I could use another measure to use as a threshold. Taking the above limit, I can create a measure like the following:

Company Eligible Sales = 
    IF(
        [Total Sales] >= 6000000,
        1,
        0
    )

This will return a 1 if the sales are on or above our threshold, but a 0 if not. The reason we use 0/1 is that the next step currently doesn’t work for TRUE and FALSE, unfortunately.

I can now add this to my visual and filter on it:

Filtering by our measure

Adding Interactivity

We’re getting better in that we’ve moved the number out of the filter but it still requires a report author to edit the measure it depends on. With the recently-added “What If” parameters, we can do better:

Parameter, ranging from 0 to 10 million, incrementing by 1 million, with a default of 5 million

This creates a table in my model with two columns:

• Sales Threshold, which contains my series data as specified above;
• Sales Threshold Value, which is a measure calculating the selected value from the Sales Threshold parameter.

I also get a slicer in my page and I’ve positioned this above the chart:

Slicer positioned up with chart

The slicer isn’t doing anything yet, so I now need to make a change to my Company Eligible Sales measure as follows:

Company Eligible Sales = 
    IF(
        [Total Sales] >= 'Sales Threshold'[Sales Threshold Value],
        1,
        0
    )

This simply binds the 0/1 value used in the filter to the selected parameter value. No further changes required!

There we go!

Wrapping Up

This is a simple use case and could be attempted with any type of measures for different results. It’ll be interesting exploring this functionality a bit more as time allows but hopefully a few people may find this handy, or find a more elegant solution with a bit of experimentation.