Power BI — Conditional What-If

This post aims to explain how we can create conditional What-If scenarios in Power BI.

Ideogram.com

If you’re not familiar with What-If in Power BI, I suggest you watch this video:

Can What If parameters help in Power BI reports? (youtube.com)

Intro

There is a “limitation” in Power BI when creating What-If scenarios: when you execute a mathematical operation over a measure, such as [Sum of Value] * [Parameter Value], this operation will only be applied to the measure itself.

The problem

Your manager wants to simulate multiple scenarios and would like to measure the impact of a certain category’s increase or decrease on his budget.

So, we need to be able to select a certain category and apply the mathematical operation over it.

How to

First of all, the semantic model:

Image 1 — Model View

And we should create a dimension table with all the values contained in the categories (CAT) from the main table.

For this to work, the category table must be disconnected from the main table. If you link them via the “CAT” column, it won’t work.

Of course, you need to check if the categories matches.

Using the category table, create a filter like this:

Image 2 — Category Filter

Next, we need to create the parameter. Go to “Modeling” > “New Parameter” > “Numeric Range”.

Parameter = GENERATESERIES(0, 1, 0.01) //Example

With the parameter created it’s now time to create the measure that will execute a mathematical operation based

Total = 
SUMX(
    'Main Table'
        ,IF(
            'Main Table'[CAT] = SELECTEDVALUE('Category Table'[CAT]),
            'Main Table'[Value] * [Parameter Value] + 'Main Table'[Value],
            'Main Table'[Value]
        )
)

The DAX code above checks if the category in the main table matches the value selected in the filter. If it does, it multiplies the value by the parameter and adds the original value. If it doesn’t match, it returns the original value.

And that’s it. We are ready.

See how it works in practice:

Image 3 — How it works

If you don’t select any filter of any parameter, nothing will happen… but if you follow the steps see the change:

Image 4 — Conditional What-If

Now you can see, only the category selected was multiplied by the parameter.

Link for the .pbix.

Conclusion

This post was only possible thanks to NaveenGandhi and the Power BI Community, please make sure to follow him and be part of the official PBI community.

Naveen Gandhi | LinkedIn

Solved: Conditional What-If — Microsoft Fabric Community

For more posts:

Igor Comune - Medium

Follow me on LinkedIn Igor Comune | LinkedIn

Don’t forget to comment, clap, and share.

Thank-you!

Igor Comune.

Don’t forget to subscribe to

👉 Power BI Publication

👉 Power BI Newsletter

and join our Power BI community

👉 Power BI Masterclass