Power BI IF + 41 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
  32. Power Automate If expression
  33. Power Automate If expression syntax
  34. Power Automate If expression example
  35. Power Automate If expression dynamic content
  36. Power Automate If expression null
  37. Power Automate If expression variable
  38. Power Automate If expression greater than
  39. Power Automate If expression equals
  40. Power Automate Multiple if statement
  41. Power Automate condition if string is empty
  42. Power Automate expression if or
  43. Power Automate expression IF length
  44. Power Automate if statement contains
Table of Contents show

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.

Import data from SharePoint list to power bi
Import data from SharePoint list to power bi

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.

Import data from SharePoint list to power bi
Import data from SharePoint list to power bi

Step-3:

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

power bi if statement
Power BI IF statement

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")
Power BI IF statement example
Power BI IF statement example

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.

Power BI IF contains
Power BI IF contains

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" )
Power BI IF with contains()
Power BI IF with contains()

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:

Power BI IF contains multiple condition
Power BI IF contains multiple conditions

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" )))
Power BI IF contains multiple condition
Power BI IF contains multiple conditions

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:

Power BI IF multiple condition
Power BI IF multiple conditions

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" )
    )
)
Power BI IF multiple condition
Power BI IF multiple conditions

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:

Power BI IF contains text then
Power BI IF contains text then

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"
Power BI IF text contains then 1
Power BI IF text contains then

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.

Power BI IF text.contains() then
Power BI IF text.contains() then

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.

Power BI IF null
Power BI IF null

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])   
Power BI IF null
Power BI IF null

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.

Power BI IF statement custom column
Power BI IF statement custom column

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]
Power BI IF statement custom column
Power BI IF statement custom column

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.

custom column using Power BI IF
custom column using Power BI IF

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.

Power BI IF AND
Power BI IF AND

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],""))
Power BI IF AND Statement
Power BI IF AND Statement

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:

Power BI IF OR Statement
Power BI IF OR Statement

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.

Power BI if blank then null
Power BI if blank then null

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()))
Power BI IF blank then null example
Power BI IF blank then null example

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])
show Power BI if blank then null
show Power BI if blank then null

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])  
Power BI if blank then text
Power BI if blank then text

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]) 
Power BI if blank then value
Power BI IF Blank then value

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:

Power BI if nan then 0
Power BI if nan then 0

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]))
Power BI if nan then 0
Power BI if nan then 0

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

NaN_Value = [TotalPrice]/ SUM('Product'[Quantity])
Power BI if divide by zero
Power BI if divide by zero

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)
show Power BI if NaN then 0show Power BI if NaN then 0
show Power BI if NaN then 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.

Power Bi if negative then zero
Power Bi if negative then zero

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]))
Power Bi if negative then zero
Power Bi if negative then zero

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]) 
Show Zero If negative value Power BI
Show Zero If negative value Power BI

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")  
Power BI if error
Power BI if-error

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:

Power BI if empty
Power BI if empty

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]))
Power BI if empty example
Power BI if empty example

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")
    )
Power BI if greater than and less than
Power BI if greater than and less than

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))
Power BI if greater than 0
Power BI if greater than 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.

Power BI if is null
Power BI if is null

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

Example of Power BI if is null
Example of Power BI if is null

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

Example of Power BI if is null
Example of Power BI if is null

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")
Power Bi if less than 0
Power BI IF less than 0

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.

Power Bi if positive green if negative red
Power Bi if positive green if negative red

Step-2:

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

Power Bi change color if positive green if negative red
Power BI change color IF positive green IF negative red

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

Change color Power Bi if positive green if negative red
Change color Power Bi if positive green if negative red

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.

Power Bi if positive green if negative red
Power Bi if positive green if negative red

Step-2:

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

Change color if Positive green and if negative red in Power BI
Change color if Positive green and if negative red in Power BI

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

Power bi change color if positive Green and If negative red
Power bi change color if positive Green and If negative red

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.

Power BI if null then another column value
Power BI if null then another column value

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])
Power BI if null then another column value
Power BI if null then another column value

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:

Calculate Power BI if null then another column value
Calculate Power BI if null then another column value

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())
Power bi if string contains
Power bi if a string contains

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.

Power BI if related is blank
Power BI if related is blank

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" )
    )
)
Power BI if related is blank
Power BI if related is blank

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.

Power BI if group by
Power BI if group by

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])
Power BI if group by example
Power BI if group by example

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.

Power BI if hasonevalue
Power BI if hasonevalue

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" ) 
Power BI if hasonevalue example
Power BI if hasonevalue example

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

Power BI if hasonevalue example
Power BI if hasonevalue example

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:

Power Bi if return null
Power Bi if return null

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

Power Bi if return null
Power Bi if return 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.

Power BI if two columns match
Power BI if two columns match

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")
Power BI if two columns match
Power BI if two columns 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.

Power BI if vs switch
Power BI if vs switch

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")
Power BI if vs switch
Power BI if vs switch

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")
Power BI if vs switch
Power BI if vs switch

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:

Power Bi if value exists in another table
Power Bi if a value exists in another table
Power Bi if value exists in another table
Power Bi if a value exists in another table

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.

Power Bi if value exists in another table example
Power Bi if a value exists in another table example

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]))
show Power Bi if value exists in another table
show Power Bi if a value exists in another table

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.

Power Automate If expression

The Microsoft Power Automate provides an IF statement that checks the logical expression or conditions in flow development. It checks whether the given statement is valid or not. If it is valid then it will return the True value otherwise it will return a false value.

Power Automate If expression syntax

The syntax of this expression is:

If(<expression>,True, False)

For example, we will insert below expression:

if(equals(1,0),'Same','Different')

Then it will return the output as ‘Different‘.

Read Power Automate save email attachment to SharePoint

Power Automate If expression example

Let’s take an example to check the condition whether it is true or not. For example, if the age of a person is greater than or equal to 18 then he is eligible to give a vote otherwise he is not eligible. Now we will see how to implement this logic through Power Automate.

Example-1: Using If expression

Step-1:

On Power Automate, first, we will trigger the flow manually. In that trigger, we will add the number as our input.

Power Automate If expression example
Power Automate If expression example

Step-2:

Now we will insert the below expression in a Compose action to check whether the person is eligible or not eligible to give a vote.

if(greaterOrEquals(triggerBody()['number'],18), 'Eligible', 'Not Eligible')
If expression in Power Automate
If the expression in Power Automate

Now we will check the flow by running it. When we will test it, it will ask us to insert the person’s age. For example, we will insert 27.

If expression in Microsoft flow
If expression in Microsoft flow

We can see the final result will come as ‘Eligible’:

If expression example in Power Automate
If expression example in Power Automate

Similarly, let’s insert the age as 12, then it will return the output as ‘Not Eligible’:

Example of IF expression in Power Automate
Example of IF expression in Power Automate

This is how to use IF expression on Power Automate flow.

Read Power Automate vs UiPath

Example-2: Using IF(condition) action

In Power Automate, there is a Condition (Control) action to implement the condition. Now we will see how to use this action to check the condition on a flow.

After triggering the flow manually, search for Condition under control action. Then add this action to our flow.

Then insert the value to check and their IF yes & IF no values like below.

Power Automate If condition action
Power Automate If then else

When we will test the flow, it will ask to insert the age. For example, we will insert the age as ’16’. As the value is not matched with the condition, so it will return the False value i.e. If no part:

Example of IF Condition in Power Automate
Example of IF Condition in Power Automate

This is how to use the If-Condition action in power Automate.

Read Power Automate conversion of string

Power Automate If expression dynamic content

Now we will see how to use the dynamic contents in IF condition on a Power Automate flow. For example, we have a SharePoint list that stores the students’ names, their numbers, and Grade. But we want to differentiate whether it is under grade A or grade B when we insert a dynamic number to the list.

Here is our SharePoint list:

Power Automate If expression dynamic content
Power Automate If expression dynamic content

Now we will create a flow that will automatically show the Grade of students according to their Total Numbers. Let’s say if the number is less than or equal to 50 then it is Grade B otherwise A, and the grade will automatically be updated on the SharePoint list.

Using dynamic content in expression in Flow If statement
Using dynamic content in expression in Flow If statement

It means when we insert any random number in the SharePoint list it will be updated automatically according to the Condition.

Now we will save the flow and test manually. Then we will insert data in the SharePoint list to see the result:

Using dynamic content in IF expression
Using dynamic content in IF expression

This is how to use dynamic content in Power Automate IF expression. You can Download this flow from here.

Read Power Automate Number Format

Power Automate If expression null

Here we will see how to deal with an empty or null value in Power Automate using IF expression. For example, if we insert any value as input then it will return that value as an output otherwise a null value will come.

Do you have any idea, how to implement this in Power Automate Flow? Fine, here is the answer.

Step-1:

In Power Automate, first, we will trigger the flow manually. Then we will add a Compose action. Here we can use any value(except integer) as input or we can use a null value by using a Null expression.

Power Automate If expression with null
Power Automate If expression with null

Step-2:

Again we will use Compose action, where we will use an expression that will check whether the input is null or not:

if(empty(outputs('Compose_2')),'Null',outputs('Compose_2'))
How to check if value is null with new condition parameters
How to check if the value is null with new condition parameters

As per the condition, if there is a null value then it will return the output i.e. ‘Null‘; Otherwise, it will return the input value as output i.e. Howdy.

Using null expression in condition Power Automate
Using null expression in condition Power Automate

Let’s take another example, where we will use a null as input:

Make a condition to check for empty fields or null values in Microsoft flow
Make a condition to check for empty fields or null values in Microsoft flow

The output will appear like below:

expression to check for blank values in power automate
expression to check for blank values in power automate

As the input was a null value, so it returned the output as Null.

Similarly, we can use this logic on Condition action in Flow:

Check a field is empty or not in MS Flow
Check a field is empty or not in MS Flow

In condition, it will check if the output of Compose-2 is null then it will return the ‘If yes’ part; otherwise it will return the ‘If no’ part.

Power Automate If Is Null
Power Automate If Is Null

This is how to use the null expression in conditions in Power Automate to check the null value.

Read Power Automate Initialize Variable

Power Automate If expression variable

Here we will see how to use the If condition to check variables in Power Automate. For example, if the variable is blank then it will give an output as ‘No value’ otherwise it will return the value assigned in the variable.

To implement this, we will assign an integer type variable in the flow:

Power Automate If expression variable
Power Automate If expression variable

Then we will use a compose action with an expression that will check whether the variable is blank or not:

if(equals(variables('VarNumber'),0),'No value',variables('VarNumber'))
Power Automate If expression blank
Power Automate If expression blank

As it is a blank variable, so the out will come as ‘No value’:

How to use if expression in Power Automate
How to use if expression in Power Automate

Similarly, if we insert any value on that variable then it will show that value as output:

If expression with blank in Power Automate
If expression with blank in Power Automate

This is how to use the If expression in a variable in Power Automate flow.

Read Power Automate convert time zone

Power Automate If expression greater than

In Power Automate, there is a logical function named ‘greater(value, compare To)’ that returns true if the first argument is greater than the second argument.

For example, we will create a flow that will check whether the date has expired or will come. To implement this, we will trigger the flow manually where we will use the date as input.

Then we will use the below expression on Compose action:

if(greater(triggerBody()['date'], utcNow('yyyy-MM-dd')),'This is date will come','This date has expired')
Power Automate If expression greater than
Power Automate If expression greater than

Let’s check this flow by entering a random date that has already gone from today’s date:

Logical function in Power Automate
Logical function in Power Automate

Let’s take another example by inserting a future date from today(i.e. 11/18/2021):

If expression greater than in Microsoft Flow
If expression greater than in Microsoft Flow

This is how to compare two dates, string, float, number using If expression greater than in Power Automate.

Read Power Automate Concatenate String

Power Automate If expression equals

A equals() function needs arguments to compare and return true if they are equals. Here we will see how the equals() function works with if expression on Power Automate.

To implement this function, we will take two values on our flow i.e. value1, value2.

Then we will use the expression to check whether the values are equal or not in a Compose action:

if(equals(triggerBody()['number'],triggerBody()['number_1']),'Both values are equal', 'Both are not equal')
Power Automate If expression equals
Power Automate If expression equals

Now our flow is ready to run. Let’s insert two values to check whether they are equals or not:

Power Automate If expression to check values equals or not
Power Automate If expression to check values equals or not

Similarly, if we will insert two different values then it will return the False(i.e. Both are not equal). This is how to check whether the values are equal or not in Power Automate.

Read How to convert decimal to whole number in Power Automate

Power Automate Multiple if statement

Now we will discuss how to use the multiple if statement in Power Automate. Let’s take an example to see how it works. For example, we have a SharePoint list having students’ name, their total numbers, and Remarks.

Our requirement is when we insert the name and total number in the SharePoint list it will be sent an email with the Remarks and then the remarks will be updated in the SharePoint list.

For remarks, we have some conditions such as if the total number is greater than or equal to 85 then remarks will be ‘Excellent’. Similarly, if the total number is greater than or equal to 70 then remarks will be ‘Very Good’, if the total number is greater than or equal to 50 then the remark will be ‘Good’ otherwise it will be ‘Fail‘.

Let’s create a flow to automate this process when an item is created in the SharePoint list. Then we will add an expression (according to our requirement) on a Compose action.

if(greaterOrEquals(triggerOutputs()?['body/TotalNumber'],85),'Excellent',if(greaterOrEquals(triggerOutputs()?['body/TotalNumber'],70),'Very Good',if(greaterOrEquals(triggerOutputs()?['body/TotalNumber'],50),'Good','Fail')))
Power Automate Multiple if statement
Power Automate Multiple if statement

In the next step, we will add an action to send an email and then add another action to update the remarks in the SharePoint list.

Power Automate Multiple if expression
Power Automate Multiple if expressions

Testing the flow

Let’s take an example to test and see how its works. For example, we will insert the student’s name and his total number:

Multiple If Statements in Power Automate
Multiple If Statements in Power Automate

We can see a mail in the outlook that will notify the result of the student with remarks :

Multiple If statements Power Automate
Multiple If statements Power Automate

Now we can see the remarks will be auto-updated on the SharePoint list:

Multiple If statements Power Automate
Multiple If statements Power Automate

Similarly, we inserted some numbers with decimal, the result will come like below:

Microsoft flow multiple If statements
Microsoft flow multiple If statements

This is how multiple if expressions work on Power Automate.

Read How to move files from OneDrive to SharePoint using Power Automate

Power Automate if condition is equal to string

Here we will see how a condition executes if it matches with the specified string. Let’s say, we have a lecturer’s list having a title(single-line column) and department(choice column).

Our requirement is when we select any department then it will send a notification to the respective lecture. For example, there are 3 departments such as IT, Management, and Architect. When we select IT, it will notify the respective lecture of IT. Similarly, it will notify the lectures of Management and Architect as well.

Let’s create the flow to implement this:

First, we will trigger the flow when an item is created in the SharePoint list. Then we will add a condition action to satisfy the condition like below:

Power Automate if condition is equal to string
Power Automate if the condition is equal to a string

This flow will run automatically when an item is created in the specified SharePoint list. After creating data, it will check whether the department is IT or Management, or architect. According to the condition, it will send the email where the condition will meet.

Testing

Let’s insert data in the SharePoint list where the department is ‘architect’.

 if condition is equal to string Power Automate
if condition is equal to string Power Automate

Now we can see it will notify the lecture assigned to the architect i.e. User5(a testing purpose user ID).

 if condition is equal to string Microsoft flow
if the condition is equal to string Microsoft flow

This is how to do Power Automate if a condition is equal to string.

Read Power Automate Delete all items in SharePoint list

Power Automate condition if string is empty

There is an empty() function that checks whether the input such as string, collection, a float is empty or not. Now we will see how to check a string variable is empty or null in Power Automate.

For this, here we will initialize a string variable like below:

power automate condition if string is empty
power automate condition if string is empty

Then we will use condition action using an expression, to check whether the string variable is empty or not:

empty(variables('VarString'))
Power Automate - Check String Variable is Empty or Null
Power Automate – Check String Variable is Empty or Null

The above flow will check if the expression is true i.e. if the string variable is empty then it executes the if yes. If the variable contains any value then it executes the if no parts.

Let’s test this flow and we can see as there is no value assigned in variable i.e. empty string so it executes the if yes part:

Checking if string is empty or null in MS Flow
Checking if string is empty or null in MS Flow

Similarly, we will test the flow by inserting a string value in a variable.

Flow Condition – check string is empty or not
Flow Condition – check string is empty or not

This is how to check a string variable is empty or not in Power Automate or MS flow.

Read PowerApps upload file to SharePoint document library

Power Automate expression if or

Here, we will see how the OR operator works with Power Automate flow. An OR operator returns true when one of the conditions is true or both conditions are true. Otherwise, it returns false.

For example, we have 2 numbers inputs, where we will put 2 conditions. One is if the first number is equal to the second number or if the first number is greater than the second number then it returns TRUE; otherwise, it returns FALSE.

Let’s create a flow to implement our requirement:

First, we will trigger the flow manually and add 2 number inputs for inserting numbers dynamically.

power automate expression if or
power automate expression if or

Then we will add a condition action to check the condition using OR operator:

power automate expression if using or
power automate expression if using or

Similarly, instead of Condition action, we can use these conditions in an expression format like below and we can use this expression in a Compose action.

if(or(equals(triggerBody()['number'],triggerBody()['number_1']),greater(triggerBody()['number'],triggerBody()['number_1'])),'True','False')
If condition using OR in Power Automate
If condition using OR in Power Automate

Now our flow is ready to test. Let’s insert two numbers to check the conditions (make sure if one of the conditions is true then it returns true otherwise it returns false). For example, here we will take 50 and 45 as two inputs respectively. Have a look at the output:

If expression using OR Microsoft flow
If expression using OR Microsoft flow

This is how to use Power Automate expression if using OR.

Read Microsoft Flow Example: Copy Files from SharePoint to PC

Power Automate expression IF length

Now we will see how a length function works with IF expression on Power Automate. A length function is used to return the number of elements in an array or string.

Let’s take an example of an array, where it will check if the length of the array is less than 10, then it will execute the TRUE value; otherwise, it will return FALSE.

So for this, first, we will take an array variable by initializing it:

power automate if length
power automate if length

Now we will add a Condition action to check the conditions:

Power Automate if expression using Length function
Power Automate if expression using Length function

Our flow is ready now. Let’s check the output by running it:

If expression using Length function on Microsoft flow
If expression using Length function on Microsoft flow

As the length of the array is 4 and it is less than 10, so it returns the TRUE value i.e. if yes part. This is how to do Power Automate expression if length.

Read Send a customized email when a new SharePoint list item is added using Microsoft Power Automate or Flow

Power Automate if statement contains

Here we will check how to check a string or array whether it contains a specified text or not. For this, we will initialize an array variable:

power automate if statement contains
power automate if statement contains

For example, we will check whether the array variable contains a specified value or not i.e. Ottawa. So we will add a Condition action to check this:

power automate expression if contains text
power automate expression if contains text

As the variable contains that value i.e. Ottawa, so it executes the If yes part.

Power Automate – how do we check if a property exists in the Array
Power Automate – how do we check if a property exists in the Array

This is how to check if a property exists or not in the array variable on Power Automate.

Read SharePoint auto generate column value using Power Automate or Flow

Power Automate if statement And

Here, we will see how to use conditional statement with AND operator on Power Automate. An AND operator returns true if both parameters are true; otherwise, it returns false. To implement this, we will take the example that was used previously.

We have already used one condition to check whether the VarArray contains a specified item(i.e. Ottawa) or not. Now, we will add another condition using And operator i.e. to check whether the length of the variable is greater than 5 or not. For this the flow will be:

Power Automate IF statement AND
Power Automate IF statement AND

Let’s run this flow to see the output:

If condition with And in Flow
If condition with And in Flow

As the length of the variable is 4 so the condition did not match and the output returns false. This is how to do Power Automate if statement AND.

Read Microsoft flow Send an email showing wrong time for SharePoint list column

Power Automate If expression in email

Here, we will see how to use if condition with email in Microsoft flow. For example, we have a SharePoint list with some data and we want that whenever the data got modified or created by someone except the specified owner, then it will notify the owner.

To implement this, we have created a SharePoint list like below:

Power Automate If expression in email
Power Automate If expression in email

Then we will create a flow that will check if the item is modified by someone except the specified then it will notify the owner.

If Statement Expression in Email Power Automate Flow
If Statement Expression in Email Power Automate Flow

Let’s create or modify an item from another user ID in the SharePoint list to check this flow.

If expression in email MS Flow
If expression in email MS Flow

As the new item is created by another user, so it will notify the owner or specified email id.

If Expression in Email Power Automate Flow
If Expression in Email Power Automate Flow

This is how to work with If expression in email in power Automate flow.

Read Microsoft flow change true to yes

Power Automate condition if date equals today

Here we will see how to check whether the date is equal to today or not. Let’s say, we have a SharePoint list based on some products orders and their delivery report like below:

power automate condition if date equals today
power automate condition if date equals today

Now we will create a flow to trigger this SharePoint list and insert a condition to check whether the order date is equal to today’s date or not. If it equals to today’s date then it will notify to the owner or a specified user about the order.

For today’s date, we have insert the expression:

formatDateTime(utcNow(),'yyyy-MM-dd')
Condition if date today is equal to date on SharePoint list
Condition if date today is equal to date on SharePoint list

Let’s run the flow manually and create a new item where the order date is as same as today’s date.

how to compare Dates in Microsoft flow
how to compare Dates in Microsoft flow

Now we can see an email is sent to the specified user to notify about the item that ordered today:

flow condition compare if today date is equal with a date on SharePoint list, if yes, send email
flow condition compare if today date is equal with a date on SharePoint list, if yes, send email

This is we can compare a date on SharePoint list whether it is today’s date or not and notify about it on Power Automate.

Read How to get manager email or name in Microsoft Flow

Power Automate condition if date is blank

Here we will see how to check whether the date column in sharepoint list is blank or not in Power Automate.

For this, we will take the SharePoint list that we have created previously i.e. SalesOrder. Now we will create a flow that will check whether the Order date is empty or not.

power automate condition if date is blank
power automate condition if date is blank

Let’s run the flow and create a new item on SharePoint list having blank date column:

Check a date field is blank or empty in MS Flow expression
Check a date field is blank or empty in MS Flow expression

As the date column is blank, we can see it will execute the if yes part.

IF function when Date is empty - Microsoft Power BI Community
IF function when Date is empty – Microsoft Power BI Community

This is a way where we can check whether the date is empty or not in Power Automate using If expression.

There is another way by using Get-items function, we can check whether the date column in SharePoint list is blank or not:

MS Flow to check Date field with NULL value in a SharePoint List
MS Flow to check Date field with NULL value in a SharePoint List

Similarly, we can see the output is coming like below:

Check if a date field is blank In Microsoft Automate
Check if a date field is blank In Microsoft Automate

As the date field in the 3rd item is empty so the condition is satisfied on 3. This is how to check if a date field is blank or not in Microsoft Automate.

You may like the following Power BI tutorials:

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?
  • What is Power Automate If expression?
  • What is Power Automate If expression syntax?
  • Give an example of Power Automate If expression?
  • How to use Power Automate If action?
  • How to use dynamic content in Power Automate If expression?
  • How to check Power Automate If expression null?
  • How to work with Power Automate If expression variable?
  • How to use logic function(i.e. greater than) in Power Automate If expression?
  • How to check whether the values are equal or not in Power Automate using If expression?
  • How to work with multiple if statements in Power Automate?
  • How to do if a condition is equal to the string on Microsoft Flow?
  • How to check a string variable is empty or not in Power Automate?
  • How to use OR operator in condition expression on MS flow?
  • How to use length function in Power Automate expression IF?
  • How to check whether the item exists or not in Power Automate?
  • How to use AND operatoe in condition expression on Microsoft flow?
  • How to use If expression in email in Power Automate?
  • How to compare a SharePoint Date with today in microsoft flow?
  • How to check if a date field is blank In Microsoft Automate?
>