If you’ve already checked out the post about the IF function (I recommend it if you haven’t used IF functions in Power BI or in Excel before), this post is about how to use multiple IF statements nested within each other to check for different scenarios in your data.

We’re going to use a very simple dataset to demonstrate the basics of a Nested IF function in Power BI in this post.

If you’d like to use the same Power BI file that I do in the below video and written instructions, you can download it here (or click the graphic below).

Video:

Written Instructions:

We’re starting off with a very simple dataset (the same one we used in the IF tutorial) here, with information about different health programs in different regions.

An IF function in Power BI works just like an IF function in Excel… it checks for a circumstance or scenario and then shows one value if that circumstance exists and another value if that circumstance doesn’t exist.

Let’s try that first so we’re sure we know how that works.

Click New measure up in the Ribbon, and we’ll write DAX code to check if a row in a column has Midwest as the Region.

MIDWEST = IF(MAX(Nested[Region]) = "Midwest", "yes", "no")

So, this IF formula has 3 parts. The first part checks to see what the MAX value of our Region column is (and it’s always going to be whatever text is in there, as it’s not a numeric column) and checks if it says Midwest. If it DOES say Midwest (the scenario being true) this measure will have a yes in it, and if not it’ll have a no.

Click enter, and let’s give this a test by dragging our new measure over to our table.

It’s working great. There’s a yes in the rows of data where it’s the Midwest.

Okay, so this works great when we have a situation where we need to check one scenario, but what if we wanted to have our measure have more than 2 values.

What if we wanted to look over there at our numeric Participants column and have more than 2 values.

So, to do this, let’s suppose we want to create a measure that checks our Participant number and has a few different values depending what the number is.

We can do this by nesting multiple IF statements.

We’re going to start this just like a regular IF measure. Click New measure and start this DAX:

PARTICIPANT LEVEL = 
IF(SUM(Nested[Participants]) >= 15, "Lots",

You can see that our IF statement has the first 2 parts. IF the number (or sum) or Participants is greater than (or equal to) 15, and if it is, that the measure should have the word Lots in it.

This IF statement isn’t finished though. We need a false value… what we want this measure to have if the participant number is not 15 or more.

This is where we’re going to nest another IF statement. If the >= 15 statement is false, we’re going to check for something else. We’re not going to put a value here… we’re going to do another IF.

Now our code (which is still not finished) will look like this:

PARTICIPANT LEVEL = 
IF(SUM(Nested[Participants]) >= 15, "Lots",
IF(SUM(Nested[Participants]) >= 10, "Some",

It’s now checking for greater than or equal to 15, but now it’s also checking what else there is. If the remaining values are greater than or equal to 10, then this measure will have the word Some.

Let’s take it one step further, but nesting another IF statement in here.

PARTICIPANT LEVEL = 
IF(SUM(Nested[Participants]) >= 15, "Lots",
IF(SUM(Nested[Participants]) >= 10, "Some",
IF(sum(Nested[Participants]) < 10, "Few", 
"Not Applicable")))

We’ve actually added a couple things here.

The first is another IF statement that checks to see if we have less than 10 participants (and pops in the word Few if we do.

THEN, we put in a false value that this Nested IF statement should have if NONE of the scenarios we added are true.

In our case, all possible numbers should be covered by our 3 IF statements, so if anything else appears, a Not Applicable will show up.

We finish off the code with 3 closing parentheses. These close out the ones that are up there right beside the IFs in each row.

Let’s click enter and then bring this measure into our table to see what happens.

It works great!

If you need to check for multiple things in a column, you can totally do it easily with Nested IF statements.

Take care everyone,

Joe.

Like these posts but need more formal (but still engaging and fun) training in Power BI? Contact me, Joe Travers or at joe@traversdata.com. I got you.