EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home Data Science Data Science Tutorials Power BI learn Power BI IF Statement

Power BI IF Statement

Priya Pedamkar
Article byPriya Pedamkar

Updated August 3, 2023

Power BI IF Statement

Table of Content
  1. What is the IF Statement in Power BI?
  2. DAX IF Statement
  3. How to Use DAX IF Statement in Power BI?
    1. Example of DAX IF Statement
  4. Power Query IF Statement
    1. Example #1: Single Condition
    2. Example #2: Multiple Conditions
  5. Things to Remember

What is the IF Statement in Power BI?

The Power BI IF statement is a function that makes decisions and returns a value after checking if the given condition is true or false. It allows you to specify a condition; if that condition is true, a specific action or calculation is performed. If the condition is false, a different action can be performed. It also enables adding new columns based on specific criteria.

For example, imagine a business that wants to give a free coupon to any customer who purchases above $500. They can use the Power BI IF statement, to check if “purchase > $500”. If the condition is true, the customer is eligible, so Power BI will display “Eligible”; if not, Power BI will display “Not Eligible”.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

In Power BI, we can use the IF function in two ways: with the DAX function and in the Power Query tool. DAX is a special language that we can use if we want to perform calculations on any data. For example, adding a set of numbers, finding the average of a group of values, etc. On the other hand, Power Query is a powerful tool that simplifies the task of sorting, organizing, and presenting data in a structured manner. For instance, we can use Power Query to sort our sales data according to the year.

DAX IF Statement in Power BI (Definition & Syntax)

The DAX IF statement allows us to create a new column in the Power BI desktop, where the data in the new column is based on whether a given condition is true or false.

Syntax of the DAX IF statement in Power BI:

IF(LogicalTest, ResultIfTrue, [ResultIfFalse])

In this syntax:

  • The LogicalTest is an expression or condition that evaluates to either true or false.
  • The function returns one value, i.e., ResultIfTrue, if the condition is TRUE, and another value, i.e., ResultIfFalse, if the condition is FALSE.

How to Use DAX IF Statement in Power BI?

Here is a simple step-by-step process of how to use the IF statement in Power BI

  • Start by selecting the column where you want to apply the IF statement.
  • Start by typing “IF()” to open the IF function.
  • Inside the parentheses, define the logical condition you wish to evaluate, ie., the condition you want to check.
  • Then, include a comma and specify the value to be returned if the condition is true.
  • Add another comma and mention the value you want the function to return if the logical test is false.

Example of DAX IF Statement

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

Let’s consider the below data of the list of individuals and their ages for voting. Here, we want to create a new column with the below condition:

If the “Age” is greater than “18”, then they are “Eligible” for voting, or else “Not Eligible”.
Example 1

Solution:

Step #1: Import Data
Firstly, make sure you have installed Power BI in your system. Now, to import this data from Excel to Power BI, follow the below steps:

  • Open Power BI -> Go to the “Home” tab -> select “Excel workbook”.
  • Select the Excel File -> click on “Load”. (The selection will appear on the extreme right side of the Power BI desktop window.)
  • Select the column you want to import to Power BI -> drag it to the center.

Example 1-2

The data will appear as shown below. We have successfully imported the data.

If the table does not appear in the above format, click on the “Data” option to view it in a table format (highlighted in the image below).
Example 1-3

Step #2: Create a Column with Condition

Right-click on the table and select “New column”, as shown below. 
Power BI IF Statement-Example 1-4

A new column is created in the table. See the below image. 

Example 1-5

Step #3: Apply the condition to the column

Now, let’s change the column’s name and apply our condition.

  • Enter the Column name as “Status” in the formula bar, as shown below.

Power BI IF Statement-Example 1-6

  • Write “IF”. The  DAX IF function statement will open.

Here we are going to break the formula and add the condition and results one by one. The below image shows the sections we must add for the IF statement. We will show the step-by-step process of writing each formula part using images.

Example 1-7

  • First, we must enter the first condition. To do this, type ‘Sheet2′[Age]>18 as shown in the image below.

Power BI IF Statement-Example 1-8

‘Sheet2′[Age]>18 condition, checks whether the number in the “Age” column of the “Sheet2” worksheet is greater than 18.

  • Now, type “Eligible”.

Example 1-9

It is the “ResultIfTrue” expression. It means if the Logical Test is TRUE, display “Eligible”.

  • Then type “Not Eligible“.

Example 1-10

It is the “ResultIfFalse” expression. It means if the Logical Test is FALSE, display “Not Eligible”.

  • Close the bracket and press “Enter”.

The formula will display the below result.

Power BI IF Statement-Example 1-11

Result: The DAX IF function in Power BI  has displayed “Eligible” for individuals with an age greater than 18 and “Not Eligible” for those below 18.

How to Write IF Statement in Power Query?

In Power Query, the “If” function can be used to add a new column with specific conditions.

Syntax of the IF Statement in Power Query:

If<condition> then <value_if_true> else <value_if_false>

In this syntax:

  • The If statement is used with then and else
  • The formula states that if the given condition is true, then display value_if_true or else value_if_false (if the condition is false).

Example #1: Single Condition

In this method, we will use the If condition in Power BI to add a new column with a specific condition. For this, we will use the “Conditional Column” feature of Power Query.

Consider the following sales data of employees in a company from different zones. Here, we need to add incentives based on certain conditions, like,

“If the sales value is > 6500 then incentive will be 300 or else incentive will be 200”.
Power BI IF Statement-Eg 1-1

Solution:

Step #1: Import Data

Follow Step #1 from Example #1 of the DAX IF statement to import data from Excel to Power BI.
Here, we have already imported the data.

Eg 1-2

Step #2: Open the Data in Power Query Editor Window
We need to open this data in the “Power Query Editor” window to add a new column based on our conditions.

For that, Go to the “Home” tab -> click on “Transform data”, -> select “Transform data” from the drop-down list.
Power BI IF Statement-Eg 2.3

The data will open in the Power Query Editor Window, as shown below.
Eg 2.4

Step #3: Add a New Column

  • Go to the “Add Column” tab and select “Conditional Column.

Power BI IF Statement-Eg 2.5

  • A dialog box of “Add Conditional Column” will open, as shown below.

Eg 2.6

  • First, enter a name for the new column, i.e., “Incentive”.

Eg 2.7

  • Next, click on the “Column Name” drop-down and choose the “Sales Amount ($)” column.
Note: The columns we imported to the Power BI window, i.e., columns selected in Step #1 will only appear in the “Column Name” drop-down list.

Power BI IF Statement-Eg 2.8

  • Next, choose the operator “ is greater than”.

Eg 2.9

  • Now mention the value as “6500”.

Eg 2.10

  • Then add the incentive amount as 300, i.e., the value if the condition is true.

Eg 2.11

  • In the else statement, add the incentive value as 200, i.e., the value if the condition is false.

Eg 2.12

The above data represents the following:
If “Sales Amount($)” “is greater than” “6500,” then display “300” or else “200”.

  • Click on the “OK” button.

You will see a new column of “Incentive” created, as shown below.

Result: The formula displays $300 as the incentive for employees whose sales are above $6,500. Likewise, it allows $200 as an incentive to those employees whose sales amount is below $6,500.

Power BI IF Statement-Eg 2.12.1

Example #2: Multiple Conditions

In this method, you will learn how to add a new column with multiple conditions using the “Custom Column” features of Power Query.

Let’s consider the result of the above example. Now, we have to create another column based on multiple conditions.

Here, if the “Sales Amount” is greater than $6500 and the zone is “South”, the employee should get an incentive of $400. Otherwise, if the sales amount is greater than $6500, but the zone is not “South”, the incentive will be $300. Else, if neither condition is met, the incentive will be $200.
Eg 2.1

We have given the above condition in table format also.

Condition Incentive should be Formula
If the sales value is greater than $6,500 and the region is “South”. $400 If [#”Sales Amount ($)”] > 6500 and [Zone] = “South” then 400
If the sales value is greater than $6,500, but the region is not “South”. $300 else if [#”Sales Amount ($)”] > 6500 then 300
If the sales value is not over 6500. $200 else 200.
Note: As you see in the above table, to compare the value of a column with a number in Power Query, we use the hash sign (#) before the column name to reference the specific column and perform the desired comparison.
Solution:

Step #1: Add a column with multiple conditions.

  • Go to the “Add Column” tab -> Click on “Custom Column”.

Eg 2.13

  • A “Custom Column” dialog box will open up. It has two main text boxes: New column name and Custom column formula.

Eg 2.14

  • Enter “Revised Incentive” under “New column name”.
  • Add the below formula under “Custom column formula”.
if [#”Sales Amount ($)”] > 6500 and [Zone] = “South” then 400 else If [#”Sales Amount ($)”] > 6500 then 300 else 200

Eg 2.15

  • Click on the “OK” button.

A new “Revised Incentive” column is added to the original data, as shown below.

Eg 2.16

Result: In the provided screenshot, you can see that for an employee in the South zone with sales exceeding $6500, the incentive is $400.

Eg 2.17

Step #3: Add this Custom Column to Power BI Window

Now, to add the new customized columns to the Power BI window,

  • Select “Apply” from the “File” tab.

eg 2.18

Result: The “Incentive” and “Revised Incentive” column is added to the Power BI, as shown below.

Eg 2.19

Therefore, the above examples show how to use the Power Bi IF statement in Power Query with single and multiple conditions.

Operators Used in Power BI IF Statement

The following are the various operators we can use in the IF statement. The table below describes each and gives a simple formula example to understand the operator’s use.

Operator Description Using Operators in DAX IF Statements Using Operators in Power Query IF Function
>  Checks if a value is greater than another value. IF(‘Marks_Table’[Marks] > 75, “Outstanding”, “Average”) If [#“Marks”] >75 then “Outstanding” else “Average”
<  Checks if a value is less than another value. IF(‘Area_Table’[Temperature] < 0, “Freezing”, “Normal”) If [#“Temperature”] < 0 then “Freezing” else “Normal”
= Checks if two values are equal. IF(‘Table_1’[Color] = “Red”, “Unsafe”, “Safe”) If [Color]= ”Red” then “Unsafe” else “Safe”
AND Checks if all the given conditions are true. IF(‘Income_Table’[Age] > 18 AND [Income] > 25000, “Eligible”, “Not Eligible”) If [#“Age”] >18 and [“Income”] > 25000 then “Eligible” else “Not Eligible”
OR Checks if at least one of the given conditions is true. IF(‘Table_2’[Gender] = “Male” OR [Gender] = “Female”, “Valid”, “Invalid”) If [Gender] = “Male” or [Gender] = “Female” then “Valid” else “Invalid”
Note: The table demonstrates examples of each IF statement, showcasing their syntax. These examples illustrate the format of the IF statements rather than representing actual data.

Things to Remember While Using Power BI IF Statement

  • The expression of the If function in Power Query is case-sensitive. This means that “HI” is not the same as “hi” and “Age” is not equal to “age”.
  • The ResultIfTrue and ResultIfFalse of the DAX IF statement in Power BI should have the same data type. Otherwise, it will show an error. For example, if the ResultIfTrue is a text and the ResultIfFalse has a number, it will cause an error.
  • If there are any mistakes in the formula, like missing parenthesis, nesting if statements incorrectly, or placing commas in the wrong location, they can lead to Expression.SyntaxError.
  • The Expression.Error occurs when an expression encounters an error during evaluation. For example, if we use a value not present in the table, it can result in an expression error.

Recommended Articles

This article is a guide to Power BI IF Statement. Here we discuss definitions of DAX IF statements, If statement Power BI and Power Query, and how to add new columns with conditions 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
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
  • Blog as Guest
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Power BI IF Statement Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

EDUCBA

Download Power BI IF Statement Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Cyber Monday Reloaded Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW