In this article, I’m going to give you a tutorial about utilizing multiple IF statements in Power BI. This short tutorial is from a specific thread in the Enterprise DNA Support Forum. You may watch the full video of this tutorial at the bottom of this blog.
In the Enterprise DNA Support Forum, members ask questions and get assistance about everything and anything related to Power BI. It’s great to see that the members here build new solutions on top of historical ones. This is how the knowledge base here in Enterprise DNA grows from within. Furthermore, most of the new users come here for guidance, especially when it comes to DAX formulas.
In this tutorial, I want to show you better ways of using IF statements inside Power BI. I’ll also demonstrate how you can take these techniques even further by adding complexity into these calculations that require the IF-type of logic.
If you’ve come from an Excel background, you can find a lot of common scenarios where IF statements are used. Most people used to write complex IF statements where multiple pieces of logic are nested into each other like this one.
But in Power BI, there are better ways of writing this kind of logic and making it easier to understand using DAX language.
Using SWITCH True Logic Instead Of IF Statement
What I originally came up with as a solution is to use SWITCH true logic. I created a video about the said technique and I also conducted a couple of workshops about it.
The SWITCH true logic enables you to calculate just like an IF statement. It produces particular results based on whether something you evaluate is true or false. If they’re true, they will return a result.
The best part of this technique is that you can make the results into a variable. You can set it up just like a text or a number, but it can also be a measure.
In the tutorial video, you can easily learn how to write the true or false logic. To access the video, just click the link or you can also search for it in YouTube on the Enterprise DNA channel.
Writing The Correct Format Of SWITCH True Logic
This is the kind of format that you should use.
You can change the name of the measure from Current Status to any measure that you want. After that, write the SWITCH function as well as TRUE. Lastly, place the logic that you want to test for true or false.
An amazing technique that you can do is to use simple ampersands (&) to have multiple evaluations for every row. This is how you use a multiple IF statement in Power BI.
In this particular example from a member, there are multiple evaluations on every row.
So, the first row here is evaluating whether this row (SALESSTATUS) is equal to “New” and whether this column (SALES_STAGE) is equal to “Design.” If this is true, then it will produce the “In Detailed Design” result.
You can see the condition for the alternative results in the bottom part of the formula. It means that if the row turns out to be false, it will produce the “On Hold” results. This calculation can be achieved using double ampersands (&&).
To sum up, the SWITCH true logic iterates through every formula in every row and returns the corresponding results.
This is a superior way of creating any logic that would be otherwise done using Nested IF statements. This technique looks much cleaner and easier to understand, especially if you need to revise it.
Changing The Results Into Any Measure
There’s one last thing that I want to share with you if you want to reiterate a certain part of the formula.
In the results part, you can evaluate something using one measure, and then return several measures, logic, or additional calculation.
I used to have an advanced example where I had a SWITCH measure which branched out into another SWITCH measure. It also evaluates each different row, and then if the results are true it will evaluate the next measure. It also evaluated another SWITCH statement within that measure.
The formula can really get tricky, but the most amazing part is that it’s written very clearly in a manner that’s easy to understand.
That’s all about it for this short tutorial. I just wanted to do a quick recap about this multiple IF statement query in the support forum. This is a simple way of introducing DAX solutions to beginners.
In short, I think this one provides an overall better solution than what you can usually do in Excel. I hope you learn something from this tutorial.