Power BI Measure IF with Examples

In this Power bi Tutorial, we will learn about Power BI Measure IF statement. Also, we will discuss:

• Power BI Measure if else statement
• Power BI Measure if statement text
• Power BI Measure if column equals
• Power BI Measure if multiple conditions
• Power BI Measure if blank then 0
• Power BI Measure if column contains
• Power BI Measure if value equals

Power BI Measure if else statement

By using the IF statement in Power BI Measure, it checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.

The syntax for Power BI Measure IF statement is:

Measure name = IF(LogicalTest, Result true, [Result False])

If the output of the LogicalTest is True, then it displays the second parameter(i.e. Result true), if it is false, then it will show the third parameter(i.e. Result false).

For Example, here we have data based on the Product’s financial report. Using the IF statement, we will evaluate whether the report is going UP or Down.

If the sum of the sale price is greater than or equal to the sum of the Manufacturing price it returns UP otherwise it returns DOWN.

Up/Down Measure Calculate = IF(SUM(financials[Sale Price]) >= SUM(financials[Manufacturing Price]), "UP","DOWN")

This is how to do Power BI Measure if statement.

Read Power bi show items with no data

Power BI Measure if statement text

Now will see how to if-else for a text field in Power BI Measure.

For example, here we will create a measure for our product column i.e. Text field. It will set a new measure value for each product.

Prouct_Measure = IF(MAX('financials'[Product])="Amarilla",1001,IF(MAX('financials'[Product])="Carretera",2001,IF(MAX('financials'[Product])="Montana",3001,IF(MAX('financials'[Product])="Paseo",4001,IF(MAX(financials[Product])="Velo",5001,IF(MAX(financials[Product])="VTT",6001,BLANK()))))))

This is how to use Power BI Measure if statement in the text field.

Power BI Measure if multiple conditions

In Power BI we can apply the IF statement having multiple conditions using a Measure.

For example, we have created a table with having the Products name and it’s no. of user like below:

Now we will create a measure that creates a category according to no. of users.

Category = 
IF (
    SUM('Table'[No.of user]) < 250,
    "Small",
    IF (
        SUM('Table'[No.of user]) >= 250
            && SUM('Table'[No.of user]) <= 1000,
        "Medium",
        IF ( SUM('Table'[No.of user]) > 1000 && SUM('Table'[No.of user]) <= 5000, "Large", "Global" )
    )
)

This is how to do Power BI Measure if multiple conditions.

Read Power bi show value as percentage

Power BI Measure if blank then 0

Now we will see how to show 0 instead of blank using Power BI Measure.

For Example, we have created a Product table with having blank columns.

Now we will create a measure, that shows 0 instead of Blank value.

Measure = IF (
    CALCULATE ( SUM ( 'product'[CY 2018] ) ) = BLANK (),
    0,
    CALCULATE ( SUM ( 'product'[CY 2018]) )
)

This is how to we can show 0 instead of Blank using Power BI Measure.

Read Countif function in Power BI Measure

Power BI Measure if column contains

In Power BI, a contains() function returns TRUE if there exists at least one row, where all columns have specified values.

Here we will see how the Contains() works in the IF statement using Power BI Measure.

For example, we have created a table based on Products, sales and, segments.

Now we will create a measure that contains only those product(i.e VTT) or their segment(i.e Government), which we want to show in our table.

Measure_If_contains = 

CALCULATE (

    SUM ( financials[ Sales] ),

    FILTER (

        'financials',

        CONTAINS ( 'financials', 'financials'[Product], "VTT" )

            || CONTAINS ( 'financials', 'financials'[Segment], "Government" )

    )

)

In the above screenshot, we can see that the measure column only showing those values that contain VTT or Government.

Read How to create a Power BI Dashboard in Microsoft teams

Power BI Measure if value equals

In Power BI, we will see how a measure works with an equal value using IF statements.

For example, here we have created a simple table based on channels and their subscripted user.

Now we will create a measure that calculates if the value is equal to 500 then it will turn to twice this value otherwise it will increment by 100. For this:

Measure_for_Bonus = IF(SUM('Product'[Subscripted User])=500,2*500,SUM('Product'[Subscripted User])+100)

Read Power BI Information Functions

Power BI Measure if column equals

Here we will see how a Power BI measure works with an equal column’s value using IF statements.

For example, we are going to use the previous Channel table. Now we will create another measure that calculates if a value of the column is equal to a particular exists value(i.e. Voot) in it, then the bonus value will be deducted by 100 and then divided by 200 otherwise it will be deducted by 200.

For this, the formula is:

Discount on Bonus = IF (FIRSTNONBLANK('Product'[Channel],1)= "Voot", CALCULATE ('Product'[Measure_for_Bonus] -100) /200, (CALCULATE ( 'Product'[Measure_for_Bonus] - 200) / 200))

You may like the following Power bi tutorials:

• Power bi gauge chart – How to use with examples
• Power BI Donut Chart
• Microsoft Power BI Combo Chart
• Power Bi key influencers
• How to use Microsoft Power BI Scatter Chart

Conclusion

From this above Power BI Tutorial, we learned all about Power BI Measure IF statement. Also, we discussed:

• Describe Power BI Measure IF else statement with example?
• How to work with Power BI Measure IF statement using text field?
• What is Power BI Measure IF having multiple conditions?
• How to work with Power BI Measure if blank then 0?
• How to work with Power BI Measure if column contains specified value?
• How to work with Power BI Measure if value equals?
• How to work with Power BI Measure if column equals value?