Power BI If Statement | If Contains Power BI

In Power BI, you can use the IF function in two ways: with the DAX function and in the Power Query editor.

In this tutorial, I will explain Power BI IF statement, how to use if statements in Power BI and Power BI if statements with text.

Also, we will see the topics below:

• Power BI if contains
• Power BI if blank then text
• Power Query if statement
• Power BI if between two values

IF Statement in Power BI DAX

In Power BI DAX (Data Analysis Expressions), the IF statement is a function that allows you to perform conditional logic within your data calculations.

It evaluates a condition and returns one value if it is true and another if it is false.

IF statement the syntax:

IF(<logical_test>, <value_if_true>[, <value_if_false>])

Where:

• <logical_test> = This is the condition or test you want to evaluate.
• <value_if_true> = This is the value or result you want if the condition (logical test) is true.
• <value_if_false> = This is the value or result you want if the condition (logical test) is false.

How to Use IF Statements in Power BI?

Let’s try using the IF statement in Power BI to see how it works with an example.

Scenario:

Let’s say you’re analyzing sales data. You have a dataset with information about sales transactions, including the product name, quantity sold, and the sales amount.

Your goal is to create a report that categorizes products into different sales performance levels: “Low Sales,” “Medium Sales,” and “High Sales.”

According to this scenario, we have an Excel file named Sales Data that contains the Product Name, Quantity Sold, and Sales Amount columns.

Follow the below-mentioned steps:

1. Open Power BI Desktop and load the data. Then, in the Data Panel, you can see the data set.

2. Under the Home tab, click the “New measure“.

3. Then, in the formula bar, put below the DAX expression.

Total Sales Amount = SUM(SalesTable[Sales Amount])

Where:

• Total Sales Amount = This is the name given to the result of our calculation.
• SUM = This is the function used to add up all the values in a specified column.
• SalesTable[Sales Amount] = This refers to the column “Sales Amount” in the table named “SalesTable”.

4. Now, let’s make an if statement in Power BI DAX. First, go to the “Home” tab and click on “New measure“.

5. Then, in the formula bar, put below the DAX expression.

SalesPerformance =
IF (
    [Total Sales Amount] < 1000,
    "Low Sales",
    IF (
        [Total Sales Amount] >= 1000 && [Total Sales Amount] < 5000,
        "Medium Sales",
        "High Sales"
    )
)

In this IF statement:

• The product is categorized as Low Sales if the sales amount is less than 1000.
• The product is categorized as Medium Sales if the sales amount is between 1000 and 5000.
• The product is categorized as High Sales if the sales amount is greater than or equal to 5000.

6. Now, we use this new column in the Power BI report to create a table visual to analyze sales performance by product category.

To do this, under the Home tab, click Visual gallery -> Click the Table.

7. Then, using the +Add data option, add the Product Name, Sales Amount, and SalesPerformance into Columns.

8. After that you can see the table visual in the report view.

That’s it! You’ve used an IF statement in Power BI to categorize products based on their sales performance.

Power BI IF Contains

Let’s consider a scenario where you want to analyze customer feedback data using a Power BI IF statement with text.

Scenario:

You work for a company that provides online courses. You collect feedback from customers after they complete a course.

The feedback is provided in the form of comments, and you want to categorize the comments into positive, neutral, and negative sentiments to understand customer satisfaction better.

According to this scenario, we have an Excel file named feedback that contains the Course Name and Comment columns.

Follow the below-mentioned steps:

1. Open Power BI Desktop and load the data. Then, in the Data Panel, you can see the data set.

2. Go to the Table view -> click the New column.

3. In the formula bar, put the below expression. Then click the Commit button.

Sentiment = 
IF (
    CONTAINSSTRING([Comment], "great") || CONTAINSSTRING([Comment], "excellent")|| CONTAINSSTRING([Comment], "fantastic"),
    "Positive",
    IF (
        CONTAINSSTRING([Comment], "average") || CONTAINSSTRING([Comment], "okay")|| CONTAINSSTRING([Comment], "super"),
        "Neutral",
        "Negative"
    )
)

In this IF CONTAINS statement:

• If the comment contains keywords like great, excellent, or fantastic, it’s categorized as Positive.
• If the comment contains keywords like average, okay, or super, it’s categorized as Neutral.
• Otherwise, it’s categorized as Negative.

4. After that, you can see a new column added to the Table view.

By using IF text contains Power BI, you can quickly categorize customer feedback based on specific keywords, helping you gain insights into customer satisfaction levels.

Power BI IF Blank then Text

Let’s see how we handle blank values in Power BI using an IF statement.

Scenario:

You want to analyze your sales data using Power BI. However, sometimes, the data is missing values, particularly in the “Product Category” field.

You want to create a report where if the “Product Category” field is blank, it should display “Unknown Category” instead.

Follow the below steps to do this:

1. Open Power BI Desktop and load the data. Then, in the Data Panel, you can see the data set.

2. In the “Modeling” tab, click on “New Column.” This will open a formula bar where you can write below DAX expression.

New Category = IF(ISBLANK('ProductType'[Product Category]), "Unknown Category", 'ProductType'[Product Category])

Where:

• New Category = This is the name given to the result of our calculation.
• IF(ISBLANK(‘ProductType'[Product Category]) = This part checks if the “Product Category” column in the ‘ProductType’ table is empty.
• “Unknown Category” = If the category is empty, this part assigns the label “Unknown Category”.
• ‘ProductType'[Product Category] = Otherwise, it uses the existing value in the “Product Category” column.

3. When you go to Table View you can see New Category added.

This is where you handle blank values in Power BI using an IF statement.

Power Query IF Statement

In the example, we see how we can use the if statement in the Power Query editor.

Scenario:

You’re managing inventory for a small online store that sells electronics. You have a spreadsheet containing information about the products you sell, including their names, prices, and quantities in stock.

You want to create a new column in your spreadsheet that categorizes each product as either “Low Stock” or “In Stock” based on a predefined threshold quantity.

Now follow the below steps to achieve this:

1. Open Power BI Desktop and load the data. Then, in the Data Panel, you can see the data set.

2. Under the Home tab, click Transform data.

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

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

5. First, enter a name for the new column, i.e., “Stock.” Next, click on the “Column Name” drop-down and choose the “Quantity” column.

Next, choose the operator “ is less than or equal to” -> Now mention the value as “10.” Then add the Output as Low Stock. In the else statement, add In Stock. Then click OK.

6. After that, you can see the stock column added in the Power Query editor.

After creating the custom column, close the Power Query Editor and choose to load.

By following these steps, you’ve effectively used the Power Query IF statement to categorize products in your inventory based on their stock levels.

Power BI IF Between Two Values

In this example, we see how to use if between two values.

Scenario:

Let’s say you want to analyze your sales data to identify products that fall within a specific price range. You want to use Power BI to filter out products whose prices fall between $20 and $50.

According to this scenario, we have an Excel file named Sales Data.

Follow the steps below:

1. Open Power BI Desktop and load the data. Then, in the Data Panel, you can see the data set.

2. Under the “Modeling” tab and click on “New column”. We’ll create a measure to identify products whose prices fall within our specified range.

3. In the formula bar, put the below expression. Then Click the Commit button.

Price Range = IF('SalesData'[Price] >= 20 && 'SalesData'[Price] <= 50, "Within Range", "Out of Range")

Where:

• Price Range = This is the name of our new column.
• IF = This function allows us to check a condition and return different values based on whether the condition is true or false.
• ‘SalesData'[Price] >= 20 && ‘SalesData'[Price] <= 50 = This part of the formula checks if the price in the ‘SalesData’ table is greater than or equal to 20 AND less than or equal to 50.
• Within Range = This text will be displayed in the new column if the price meets the condition (between 20 and 50).
• Out of Range = If the price doesn’t meet the condition (less than 20 or greater than 50), this text will be displayed in the new column.

4. When you go to the table view you can see a Price Range column created.

Now, You’ll see a list of products categorized as either “Within Range” or “Out of Range” based on their prices.

Power BI IF Statement With Text

Using IF statements in Power BI with text involves creating calculated columns or measures that evaluate a condition and return different values based on whether the condition is met or not.

Scenario:

Let’s say you have a dataset containing information about customer satisfaction scores for a company’s products.

You want to categorize the satisfaction scores into “Satisfied,” “Neutral,” and “Dissatisfied” categories based on predefined thresholds.

Now follow the below steps:

1. Open Power BI Desktop and load the data. Then, in the Data Panel, you can see the data set.

2. Click on ‘New Column’ in the ‘Modeling’ tab to create a new column in our data table.

3. In the formula bar, put the below expression. Then Click the Commit button.

Satisfaction Category = 
IF(CustomerSatisfaction[Satisfaction Score] >= 7, "Satisfied",
   IF(CustomerSatisfaction[Satisfaction Score] >= 4, "Neutral", "Dissatisfied"))

In this IF statement:

• Satisfaction scores of 7 or higher are categorized as “Satisfied.”
• Scores between 4 and 6 (inclusive) are categorized as “Neutral”.
• Scores below 4 are categorized as “Dissatisfied”.

4. Now you can see in the Table view the Satisfaction Category created.

This way, you can use Power BI IF Statement with Text.

Also, you may like:

• Create a Power BI Pie Chart
• Power BI pie chart conditional formatting
• Line Chart in Power BI
• Power BI Slicer Sort Descending
• Card Visual in Power BI
• Change yyyymmdd to Date Format in Power BI

Conclusion

In this Power BI tutorial, we covered the IF statement in Power BI. We also talked about the following topics:

• how to use if statements in Power BI
• Power BI if contains
• Power BI if blank then text
• Power Query if statement
• Power BI if between two values