EDUCBA

Home » Data Science » Data Science Tutorials » PowerBI Tutorial » Power BI IF Statement

Power BI IF Statement

Introduction to Power BI IF Statement

IF is the most popular statement in Excel & Power BI. Arriving new columns based on multiple conditions is almost impossible without IF Statements, so one needs to be aware of if statements while arriving new columns.

In Power BI IF statement is used as DAX function as well as Power Query conditional column, so in this article, we will take you through how to use the IF statement in Power BI to arrive new conditional columns.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

What is the IF Statement in Power BI?

IF is a logical function or statement which is often used in MS Excel, coding languages and also in Power BI. In Power BI it is available in two ways, one is in terms of DAX function and another one is in terms of Power Query tool to add a new column based on conditions.

You must have put hours while learning it in MS Excel but with Power Query editor you will find it dead easy. Also, one thing we assume while writing this article is you are already aware of IF function in MS Excel.

How to Use the IF Statement in Power BI?

Below are the different examples to use If Statement in Power BI:

Power BI IF Statement – Example #1

For an example look at the below data table for this demonstration. So you can download the excel workbook from the below link which is used for this example.

You can download this Power BI IF Statement Excel Template here – Power BI IF Statement Excel Template

Power BI IF Statement Example 1-1

Now we need to add a new column “Incentive” based on certain condition and that condition is as follows.

“If the sales value is >6500 then incentive will be 300 or else incentive will be 200”.

  • Now I have already uploaded the above data table to Power BI Desktop file.

Power BI IF Statement Example 1-2

  • Now we need to go to the “Power Query” editor to arrive conditional column. Under the Home tab click on Edit Queries.

Edit Queries Example 1-3

  • This will take you to the Power Query Editor window.

Power Query Editor window Example 1-4

  • In this new window go to the Add Column tab and choose Conditional Column.

Choose Conditional Column Example 1-5

  • This will open a new conditional column criteria window as shown below.

Power BI IF Statement Example 1-6

  • First, give a name to this column as “Incentive 1”.

Power BI IF Statement Example 1-7

  • After this, we can see that the if statement already exists, so from the “Column Name” drop-down choose the “Sales Value” column.

Power BI IF Statement Example 1-8

  • Next, choose the operator as greater than (>).

Power BI IF Statement Example 1-9

  • Now mention the value as “6500”.

Power BI IF Statement Example 1-10

  • Then the output will be an Incentive amount of 300.

Power BI IF Statement Example 1-11

  • In the else statement add the incentive value as 200.

Power BI IF Statement Example 1-12

Now we can read this if statement with clear English i.e. “If Sale Value is greater than 6500 then Output is 300 Else 200.

This looks a lot easier than regular IF condition in MS Excel isn’t it??? Click on Ok to have a new conditional column.

Power BI IF Statement Example 1-13

There you go we have a new column as “Incentive” based on the given if conditions in Power BI.

Power BI IF Statement – Example #2

Now we have seen how to use if statement to arrive at a new column. Similarly, imagine the situation of arriving a new column based on multiple conditions.

For example “if the sales value is >6500 and region is “South” then we need the Incentive value as 400 or else if the sales value is >6500 then the incentive is 300 or else 200.

  • So, in this case, we need to use the “Custom Column” option.

Use Custom Column option Example 2-1

  • This will open up the below window for us.

Power BI IF Statement Example 2-2

  • As usual, give a name to New Column as “Incentive 2”.

Incentive 2 Column Example 2-3

  • Now in the “Custom Column Formula” space, we need to write the “if Statement”, so first write the if statement and chose the Sale Value column.

Custom Column Formula Example 2-4

  • The first criteria are if the sale value is >6500, apply this logical test.

Power BI IF Statement Example 2-5

  • Now we need to apply one more logical condition as well i.e. if the region is “South”, we need to use AND statement here.

Power BI IF Statement Example 2-6

  • Then the Incentive will be 400.

Power BI IF Statement Example 2-7

  • Else if we need to do another logical test of whether sales value is >6500 or not, if yes then the incentive will be 300 or else 200. So apply one more if statement like the below.

Power BI IF Statement Example 2-8

Note: Make sure all the formula syntax is in the lowercase letter.

Ok, in this statement first we will test whether sales value is > 6500 and region is “South’ or not if both are correct then the Incentive amount will be 400 or else again it will test whether sales value is > 6500 or not if yes then incentive will be 300 or else 200. Click on Ok to get the result.

Power BI IF Statement Example 2-9

  • To see the difference of this Incentive column look at row number 7 where sales value is > 6500 and region is “South” so the Incentive value is 400.

Power BI IF Statement Example 2-10

  • Once the changes are applied click on “Close and Apply” under the Home tab.

Close and Apply Example 2-11

Now we have new columns with an older column in the Power BI Data Model.

NOTE: Power BI IF Statement file can also be downloaded from the link below and the final output can be viewed.
You can download this Power BI IF Statement Template here – Power BI IF Statement Template

Things to Remember

  • IF is available as statement and DAX function in Power BI.
  • In a statement, all the syntax should be in lower case characters, if any upper case included formula will show error and you can close out the formula.

Recommended Articles

This is a guide to Power BI IF Statement. Here we discuss how to use If statement in Power BI to arrive new column along with practical examples. You may also look at the following articles to learn more –

  1. Icons in Power BI Desktop
  2. How to Create Reports in Power BI?
  3. Power BI Tutorial for Beginners
  4. How to Use Calendar DAX Function in Power BI?
  5. How to Use Filter DAX Function in Power BI?
  6. Top Features of Power BI for Data Visualization
0 Shares
Share
Tweet
Share