Power BI IF + 31 Examples

In this Power BI Tutorial, we will learn about Power BI IF statement. Also, we will discuss how to use this on Power BI and below topics:

1. Power BI IF statement
2. Power BI IF contains
3. Power BI IF multiple conditions
4. Power BI IF contains multiple conditions
5. Power BI IF contains text then
6. Power BI IF null
7. Power BI IF else statement custom column
8. Power BI IF And
9. Power BI IF OR
10. Power BI if blank then null
11. Power BI if blank then text
12. Power BI if blank then the value
13. Power BI if divide by zero
14. Power BI if nan then 0
15. Power Bi if negative then zero
16. Power BI IF Error
17. Power BI if empty
18. Power BI if greater than and less than
19. Power BI if greater than 0
20. Power BI if is null
21. Power Bi if less than 0
22. Power Bi if positive green if negative red
23. Calculate Power BI if null then another column value
24. Power bi if a string contains
25. Power BI if related is blank
26. Power BI if group by
27. Power BI if hasonevalue example
28. Power Bi if return null
29. Power BI if two columns match
30. Power BI if vs switch
31. Power Bi if a value exists in another table

Power BI IF statement

The IF statement is a kind of logical statement in Power BI. It checks a condition and returns the first value if it is True otherwise it returns the second value i.e. False.

For the IF statement the syntax is:

IF(<Logical Test>,[ResultTrue],[ResultFalse])

Let’s have an example using the IF statement and see how it works on Power BI. For this, here is a step-by-step guide to follow this:

Step-1:

We have prepared a SharePoint list based on the Product’s order and their details. Here is its excel format file.

Step-2:

Open your Power BI Desktop, then go to get data. Select online services. Then Click on SharePoint Online List and then Connect. Insert your SharePoint site URL and click ok.

It will connect to your SharePoint site and show all the list that contains. Select the Orders list and press on Load.

Now we can see the SharePoint is being loaded on Power BI Desktop.

Step-3:

Here we will create a table using Customer name and Order quantity. And the table is:

According to the order quantity, we will calculate which customer will get a surprise gift and which will not. If the order quantity of a customer is greater than 50 then he will eligible to get the surprise gift.

Result = IF(SUM(Orders[Order Quantity]) >= 50, "Get Surprise gift","Better luck next time")

We can see as per our condition it differentiate the result which customer will get the surprise gift and which will not.

This is a simple example to show how an IF function works on Power BI.

Read Power BI Pie Chart

Power BI IF contains

In Power BI, a CONTAINS() is a kind of information function that returns true if values for all referred columns exist, or are contained, in those columns; otherwise, the function will return false.

Now we will see how the Contains() function will work on Power BI IF() function. For this, again we will create another table using only the Products category and Ship Mode.

Here, we will create a measure that will returns if the Ship mode contains “Express Air”, then it will return “Deliver soon”; otherwise, it will return “Shipped”.

Deliver Update = IF ( CONTAINS ( Orders, Orders[Ship Mode], "Express Air" ), "Deliver soon", "Shipped" )

In the above table, we can see the measure differentiates the deliver update according to condition. This is how to work Contains() with Power BI IF function.

Read Power BI Switch

Power BI IF contains multiple conditions

We saw that how a Contains() function works with Power BI IF(). Here we will how a Contains() function works with Power BI IF() having multiple conditions.

To implement this, here we have created a table using sample data like below:

Now we will create a measure that will return the result if that contains Office Supplies or Express Air.

Price = CALCULATE ([Total Price],FILTER ('Orders',CONTAINS ( 'Orders', 'Orders'[Product Category], "Office Supplies" ) || CONTAINS ( 'Orders','Orders'[Ship Mode], "Express Air" )))

The above chart, it is showing us only those prices if the field contains Office Supplies or Express Air. This is how to use Contains() function with Multiple conditions in Power BI IF function.

Read Power BI Card – How to Use

Power BI IF multiple conditions

Here we will see how to use multiple conditions in Power BI IF function. Let’s have a look at the below example. To implement this, we are going to create a table having columns like:

Now we will create a measure that will calculate based on profit value and defined which category reached the target or is in progress according to our multiple conditions.

Target = 
IF (
    SUM('Orders'[Profit]) < 2500,
    "Need progress",
    IF (
        SUM('Orders'[Profit]) >= 2500
            && SUM('Orders'[Profit]) <= 10000,
        "Good",
        IF ( SUM('Orders'[Profit]) > 10000 && SUM('Orders'[Profit]) <= 50000, "In Progress", "Target Reached" )
    )
)

Using multiple conditions it returns whether the target is reached or needs progress or is in progress. This is how to use multiple conditions on Power BI IF.

Read Power BI Measure Sum and Subtract Example

Power BI IF text contains then

There is an important text function available on Power BI i.e. Text.contains. It detects whether the text’s text contains the text’s substring. It returns true if the text is found. And the syntax for this function is:

Text.contains(text as nullable text, substring as text, optional comparer as nullable function)

We will see how to use this function with IF in Power BI with a suitable example. Here we have created a table using our sample data:

Now we will create a custom column to use this text. contains() to apply conditions on our table data.

To create a custom column, on Power BI Desktop > Transform Data > Power Query Editor page will open > select the Add column > click on the custom column.

In the formula box, insert the below formula and click on OK. It will create a custom column on the date table.

= if Text.Contains([[Ship Mode]], "Regular Air") then "Late Deliver" else "Speed Deliver"

After clicking on OK, go to the Home tab and press on Close and Apply. Now on the Power BI Desktop, we will add this custom column to see the result.

As we can see our data got updated after applying text. contains() on Power BI. In this way, we can use text.contains() function with IF() on Power BI.

Read Remove blank from Power bi slicer

Power BI IF null/blank then 0

Now we will see how to deal with null value on Power BI IF function. For this, we have created a SharePoint list and import this to Power BI Desktop or you can download this from here.

First, we will create a table having both columns. Then we will create a calculated column using DAX, which returns if the result has a NULL or blank value then it will return a defined value.

UpdatedResult = IF(ISBLANK('Student Results'[Result]), 0, 'Student Results'[Result])   

Here we defined if the result has no value then it will return 0. This is how to work with a NULL value in Power BI IF.

Read Difference between USERNAME() and USERPRINCIPALNAME() in Power BI Dax

Power BI IF else Statement Custom Column

Here we will see how to create a custom column with Power BI IF statement. For this, again we are going to use the previous table i.e. Student results.

Now we will create a custom column that will return if the result has no value then it will return “FAIL”.

To create a custom column, we have to follow these steps:

On Power BI Desktop, go to Transform Data. It will open the Power Query Editor. Here we will select the Add column tab, then click on the custom column. In the formula box, insert the below formula and click on OK.

= if[Result] = null then "FAIL" else[Result]

Now we can see our data got updated with a new custom column. We will add this custom column to the result in the table on Power BI.

This is how we can use Power BI IF function in a Custom column.

Read How to create and use Power BI Bookmarks

Power BI IF And

An And is a kind of logical operator in DAX that accepts two arguments. It is represented as double ampersand i.e. &&. It checks whether both arguments are TRUE, and returns TRUE if both arguments are TRUE. Otherwise, it returns FALSE.

Here we will see how to use AND function in a Conditional statement in Power BI. To implement this here, we have created a table using our sample date i.e. Orders.

Now we will create a calculated column using DAX, that will calculate the total order quantity of those products that meet the specified conditions.

Specified Quantity = 
CALCULATE(SUM(Orders[Order Quantity]),
IF(Orders[Customer Segment] = "Corporate"
&& Orders[Product Category] = "Technology", Orders[Order Quantity],""))

According to our sample data, there is only one data that contain both Corporate and Technology and it is showing the total product quantity of that specified column’s data.

This is how to use AND operator in the power BI IF statement.

Read What is the difference between calculated column and measure in Power BI

Power BI IF OR

We have seen how the AND operator works with conditional statements. Now we will see how the OR operator works with the IF statement in Power BI DAX. Before that first, we will know how an OR function works?

An OR is a kind of logical operator in DAX that accepts two arguments. It is represented as double pipelines i.e. II. It checks whether one of the arguments is TRUE then returns TRUE. The function returns FALSE if both arguments are FALSE.

Similarly, we will use OR operator instead of AND operator and see changes. For this, here we will create another calculated column:

Specified Quantity = 
CALCULATE(SUM(Orders[Order Quantity]),
IF(Orders[Customer Segment] = "Corporate"
|| Orders[Product Category] = "Technology", Orders[Order Quantity],""))

We can see, it will show the result as SUM of order quantity, which contains Corporate or Technology:

This is how an OR operator works with Power BI IF statement.

Read Power bi free vs pro vs premium

Power BI IF Blank then null

In DAX, we use Blank to define the null or empty value in a special way. Here we will see how to deal with Blank value using a conditional statement in Power BI. In order to return value, we use a function as “BLANK()“. To implement this, we have created a SharePoint list based on Bike’s sold.

Then we will import this SharePoint list to Power BI Desktop. Now we will create a measure that will show the blank value on the table according to our condition.

TaxToPay = SUMX(BikeSells, IF(BikeSells[BikeSold]>100,BikeSells[BikeSold]*0.3,BLANK()))

This is column showing us the calculatedvalue of those data, if that data is greater than 100, then it will pay the tax. Otherwise, it will show blank.

Now we will create another measure that will show null if the data is blank. For this:

Show_null = IF([TaxToPay] = BLANK(), "null", [TaxToPay])

In this way, we can show null if the value is Blank in a data table using Power BI Conditional statement.

Read How to use weekday function power bi with example

Power BI IF Blank then text

Similarly, we will see how to show some random text instead of blank value. For this we will create another measure:

Show text = IF([TaxToPay] = BLANK(), "Other", [TaxToPay])  

This is how we can show some text over the blank value using DAX.

Read Contact your admin to enable embed code creation Power bi

Power BI IF Blank then value

Here we will see how to replace the blank with a value in a data table. For this again we will create a measure to show a value instead of a blank value.

Show_value = IF([TaxToPay] = BLANK(), 0, [TaxToPay]) 

If we insert another number such as 4,5,10…. then it will appear as decimal number. This is how we can show a specific value over the blank in the data table.

Read Power BI convert hours to minutes

Power BI IF Divided by 0

In mathematics, when we divide 0 with 0 a NaN value will create. It is undefined as a real number. NaN stands for Not a Number. It is a member of numerical data type.

Now we will see what is happening if we divide a number with 0 and O with O in Power BI using Measure. For this here we have create a SharePoit list based on Product’s name, quantity and Price like below:

Now we will import this SharePoint list to Power BI Desktop. Then we will create a measure that will calculate the total price:

TotalPrice = CALCULATE((SUM('Product'[Quantity]))* SUM('Product'[Price]))

Let’s create another measure that will calculate and show us the NaN on the data table.

NaN_Value = [TotalPrice]/ SUM('Product'[Quantity])

When we try to divide a number(in total price) with another number(in Quantity) it shows the value as usual. But in the case of a divide between 0 with 0, it returns a NaN value. This is how we can calculate the division by 0 in Power BI.

Read Power BI convert yyyymmdd to date

Power BI if NaN then 0

After getting NaN value, now we will see how to show 0 instead of NaN.

For this we will create another measure:

0_instead of_NaN = IFERROR('Product'[NaN_Value], 0)

This is how to show Power BI IF NaN then 0.

Power Bi IF negative then zero

Now we will see how to deal with negative value in Power BI using DAX. For example, here we have created a table having two column such as Profit and loss with some random values.

Now we will create a measure that will calculate the profit or loss based on the salePrice and ActualPrice:

Profit/loss = CALCULATE(SUM(salesTable[ActualPrice]) - SUM(salesTable[salePrice]))

Again we will create another measure that will return 0 if is there any negative value.

Measure = if('salesTable'[Profit/loss]<0,0,[Profit/loss]) 

In this simple way, we can Show Zero If there is negative value Power BI.

Check out, Get Month Name from Month Number in Power BI

Power BI IFERROR

Now we will see how to IFERROR function in Power BI. It returns value_if_error if the first expression is an error and the value of the expression itself otherwise.

The syntax for this function is:

IFERROR(Value, valueIfError)

For example, we will create a measure that will show whether the value is true or false according to our condition. It will return a specified value if the expression returns an error. Otherwise, it will return the value of the expression itself.

IFERROR_Measure = IFERROR([Profit/loss]>0,"True")  

This is how the IFERROR measure works on Power BI.

Power BI IF Empty

In Power BI, there is another function known as “ISEMPTY()“. It returns true if the specified table or table expression is empty. The syntax for this function is:

ISEMPTY(TABLE)

Let’s take an example to see how this works on Power BI. For example, here we have created a table having some columns with empty data like below:

Now we will create a measure using this function, to check whether the table is empty.

IsEmpty = IF(ISEMPTY(CALCULATETABLE('Table','Table'[Quantity])),"NoValue",SUM('Table'[Quantity]))

This is how we can use ISEMPTY() function in Power BI.

Read How to use Power BI sync slicers

Power BI IF greater than and less than

In Power BI, the Greater than(>) and less than(<) operators go under the Comparison operator. It uses to compare between two values.

Now we will see how both operators work in Power BI with the condition. For this, we are going use the student table that we have created previously. Then we will create a measure that defines the grade according to results.

For example, if the result is greater than or greater than equal to 70, then the grade is “A”. Similarly, if the result is less than 60, then it is “Fail”. Otherwise, mid results are considered as “Good”.

Grade = 
    IF (
        SUM('Student Results'[Result]) >= 70,
        "GradeA",
        IF ( SUM('Student Results'[Result]) < 60, "Fail", "Good")
    )

This is how to use Power BI if greater than and less than.

Power BI If greater than 0

Let’s take another example, to count the total no. of qualified students. If the result is greater than 0 then it is qualified.

For this, here we will create a measure that will count the total qualified students:

Qualified = CALCULATE(COUNT('Student Results'[Result]),FILTER('Student Results','Student Results'[Result]>0))

This is how we can calculate the nonzero data using greater than 0 in Power BI.

Power BI IF is Null

Sometimes, we can see if there are blank data inside the data table, then it represents a Null Value. So the question is how to handle this if there is a Null? Here is the simple answer, we can replace a number with Null without any code.

For this, go to Transform Data(Power query editor) > select the Column > Transform tab > Replace values.

Then it will open a page where we can set a value to replace the Null.

Now we can see the null vale got updated with 0.

This is how we can easily replace a number with a Null value.

Read Power BI Calculated Column Example

Power BI IF less than 0

Now we will see how to handle data if the data is less than 0. Here we have already a table having some prices with profit/loss values.

In that table, we will create a measure that will differntiate the value whether it is profit or loss. For example, if it is a negative value then it returns the value as “Loss”, otherwise it is “Profit”.

Profit_Loss = IF([Profit/loss]<0, "Loss", "Profit")

This is how we can use this comparison opertor i.e. less than( < ) in Power BI.

Power BI IF Positive green IF Negative red

Now we will see how to change the background color based on number. For example, if the value is a Positive number then it should be color as green color. If the numer is a negative number then it should be red.

Example-1: Change Background color Power Bi if positive green if negative red

Here we are going to take this previous example table based on Profit /loss value. To chnage the background color we have to follow below steps:

Step-1:

Click on the Profit/loss measure > Conditional formatting > Background color.

Step-2:

It will open a conditional formatting page, where we can put condition to change the background color like below:

When we click on OK, it will change the background of the measure on the table like below:

This is how we can change the background color if positive green if negative red in Power BI.

Example-2: Change font color Power Bi if positive green if negative red

Like background color, we can format the font color in Power BI i.e. if it is a Posititive number then it will show the green number and if it is a negative number then it will show the red color.

Step-1:

For this, Click on the Profit/loss measure > Conditional formatting > Font color.

Step-2:

Then it have to put condition to format the font color like below:

After clicking on OK, we can see the font color got formatted according to positive value and negative value.

This is how we can change the font color if positive Green and If negative red in Power BI.

Read Power bi conditional column example

Power BI IF null then another column value

In Power BI, now we will see how to handle the null value and how to show another column value instead of a null value. To implement this, here we have created a table having some columns with null data.

Now we will create a calculated column using DAX, that will replace the null value with another column value.

UpdatedValue = IF('Table'[NewPrice] = BLANK(), 'Table'[OldPrice], 'Table'[NewPrice])

We can see the updated value got to replace the null value with the old price. Also, we can show the updated value using an arithmetic operation of another column.

For example, we want the null value should be twice another column value. For this, we will create another column:

UpdatedValue = IF('Table'[NewPrice] = BLANK(), 'Table'[OldPrice]*2, 'Table'[NewPrice])

We can see the null value got updated with twice the old price:

This is how to show and Calculate Power BI if null then another column value.

Read How to split column in Power bi

Power BI if String contains

In DAX, there is another function in the information function known as “CONTAINSTRING()”. This function returns true if find_text is a substring of within_text; otherwise, it returns false. This function is not case-sensitive.

The syntax for this function is:

CONTAINSTRING(<within text>, <find text>)

Let’s take an example to implement this function. Here we are going to use the student result table that we have created.

Now we will create a measure that will return “True” if the string is GradeA. Otherwise, it will return false.

String_contains = IF(CONTAINSSTRING([Grade], "GradeA"),TRUE(),FALSE())

This is how to works with Power bi if a string contains.

Power BI IF related is blank

Here we will see how to identify a blank in a related column. For this here we are going to use a table that already we have created to implement examples.

Now we will create a measure that will identify a blank value in a related column according to our condition.

Blank in related = IF (
    ISBLANK (SUM('Table'[NewPrice])),
    "N/A",
    IF (
        SUM('Table'[NewPrice])= SUM('Table'[OldPrice]),
        "Same",
        IF ( SUM('Table'[OldPrice]) < SUM('Table'[NewPrice]), "Higher", "Lower" )
    )
)

This is how to do Power BI if related is blank.

Read How to change data source in Power Bi

Power BI if group by

Here we will see how to group our data if there is a number of data with the same data in a table. To implement this, here we are going to use our sample data based on orders.

Now were will see how to group and calculate the total sales as per product category and per month. For this here we will create a calculated table:

Table 2 = GROUPBY(Orders,Orders[Product Category],Orders[Sales],Orders[Order Date].[Month])

This is how to do Power BI if group by.

Read Power BI Quick Insights

Power BI IF Hasonevalue

HASONEVALUE(), is a kind of information function that returns TRUE when the context for the column name has been filtered down to one distinct value only. Otherwise, it returns FALSE.

Now we will see how this function works with a conditional statement. To implement this, we are going to use our sample data.

First, we will create a slicer using the customer statement data.

Now we will create a measure that will show the random data when we select any value from the slicer.

Option = IF ( HASONEVALUE( Orders[Customer Segment] ), VALUES( Orders[Customer Segment]), "Not selected" ) 

Similarly, if we don’t select an option then it will show the else value i.e. “Not selected”.

This is how we can use the HASONEVALUE function with Power BI IF.

Power Bi if null return null

Here, we will see how to return a null value if there is already a null value. For this, again we will take the student table.

On that data table, we will create a custom column to implement this. To create a custom conditional column, go to Transform data, it will open the Power query editor. Then click on add column > Conditional column. Here we can insert the data like below:

After clicking on OK, we can see our data table got updated according to condition and it will return null if it is null.

This is how to do Power Bi if null returns null.

Read Power BI Group By Examples

Power BI IF two columns match

In this Power BI IF example, we will see how to work if two columns match in a table. For this, we have created a table having some random values.

Now we will create a calculated column that will return “Match“if the prices same in both price columns. Otherwise, it will return “Not Match“. To execute this, we will use a comparison operator.

Comp_Col = IF('Table'[OldPrice] = 'Table'[NewPrice], "Match", "Not Match")

This is how to do Power BI if two columns match.

Power BI IF vs Switch

In Power BI, where the If-Else expression uses multiple statements for multiple choices, there a Switch expression uses a single expression for multiple choices.

An If function checks a condition, and returns one value when

Let’s take examples to implement both the IF and Switch functions in Power BI.

Example-1: Power BI IF example

For this, here we are going to use the below table, that we have created previously.

Now we will create a measure using Power BI IF, it will show whether the New Price is increased or not as comparable to Old Price.

increase/decrease = IF(SUM('Table'[OldPrice])<SUM('Table'[NewPrice]), "Increased", "Decreased")

This is how the IF() function works on Power BI.

Example-2: Power BI Switch() example

To implement the Switch() function in Power BI, we are going to use the student table, that we have created.

Now we will create a measure that will show whether it is A+, B+, F according to expression.

_Switch_ = SWITCH([Grade],
            "GradeA","A+",
            "Good", "B+",
            "F")

This is how a switch() function works on Power BI.

Read Power bi shared dataset permissions management

Power Bi if value exists in another table

In this example, we will see how to handle if a value exists in another table. For example, we have two tables. One table has JobID and another one has JobID with Designations like below:

Now we will create a relationship between these two tables. To create a relationship, go to model page > right click on Job > manage relationships > new.

Then we will check whether a value exists in another table or not. If the value is there then how many times it got repeated in another table. For this here we are going to create a calculated column under table1(i.e. job):

If_value_exits = CALCULATE( COUNTROWS(JOB2), FILTER( JOB2, JOB2[ID] = Job[ID]))

It is showing in numbers according to the existence of IDs. This is how to show Power Bi if a value exists in another table.

You may like the following Power BI tutorials:

• Scheduled Power BI report data refresh

Conclusion

From this Power BI Tutorial, we learned all about Power BI IF Statement. Also, we discussed these topics:

• What is Power BI IF statement with an example?
• How to do contains in Power BI IF?
• How to do multiple conditions Power BI IF?
• How to work with Power BI IF contains multiple conditions?
• How to work with Power BI IF contains text then?
• How to do Power BI IF null?
• How to execute Power BI IF else statement custom column?
• How to work with Power BI IF And?
• How to work with Power BI IF OR?
• How to return null insted of blank in Power BI?
• How to return text insted of blank in Power BI?
• How to return value insted of blank in Power BI?
• How to do Power BI if divide by zero?
• How to return 0 if there is NaN in Power BI?
• How to return 0 if there is negative in Power BI?
• How to work with Power BI IFError?
• How to work with Power BI if empty?
• What is Power BI if greater than and less than?
• How to do Power BI if greater than 0?
• What happened if is null in Power BI?
• How to do Power Bi if less than 0?
• How to do formatting in Power Bi if positive green if negative red?
• How to Calculate Power BI if null then another column value?
• How to do Power bi if a string contains?
• How to execute Power BI if related is blank?
• What is Power BI if group by?
• Give an example on Power BI if hasonevalue.
• How to return null in Power Bi if?
• How to show if two columns match in Power BI?
• What is Power BI if vs switch?
• How to show if a value exists in another table in Power BI?