The IF function in DAX is at once one of the simplest DAX functions and also one of the most powerful. If you’ve used the IF Excel formula in spreadsheets before, you’ll be happy to know that the IF DAX function behaves exactly the same.
We’re going to use a very simple dataset to demonstrate the basics of the IF function in Power BI in this post.
If you’d like to use the same data that I do in the below video, here is the data file you can connect to a new blank file in Power BI Desktop:
Video:
Post:
We’ll start with a blank report with our Excel file connected. It’s a nice easy dataset, with just 3 columns. Let’s put them into a table just to see what they look like while we learn the basics of an IF function.
We have a Region field, a Program field, and Participants field that is numeric.
Let’s suppose we need to make a new column for our table, and we need the column to have information about if the program in a row of data is a Health program or not.
We can use an IF function for this.
First, we can right click on our data in the Data Pane and select the “New column” choice. Then in the formula bar that appears above our Canvas we’ll write some DAX:
An IF function has 3 parts. The first part (after where it says IF) is a logic statement. In this case we’re telling it to check IF the value in our Program field is “Health”. If it is, then we get a True value(which is the 2nd part of the function) as our new column value, and we’ve put a “yes” (in double quotes, as that denotes it as text"), and then the last/third part of the function is a False value, or what we want this column to hold if it does NOT say “Health” in the Program field.
After writing your DAX, hit ENTER, and you’ll get a new column in your data called “Health Program”. Add that to your table and you can see a “yes” for every row with Health Program data, and a “no” for every other row.
Pretty cool, right?
Let’s try another quick one, but this time with numbers. Let’s use the numbers of participants and make an IF statement that creates a column that says “enough” if there are 15 or more participants, and “needs more” if there are less than 15.
The DAX for our new column (which I’ll name “AMOUNT”) would look like this:
We are getting Power BI to look at how many participants there are, and if the number is greater than or equal to 15, then this new measure will have the word “enough” in it. If not, it’ll say “needs more”.
Awesome. Those are the basics of the IF function. At once really easy but also really powerful.
Let me know if you have questions about the IF function in the comments below and I’ll help you figure it out.
Take care everyone,
Joe.
Post Tags: DAX | See all tags
Contact Joe | TraversData.com | People-Friendly Power BI | LinkedIn
This post was made with the June 2024 version of Power BI Desktop.