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.

if statement in dax

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.

power bi if contains

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

if contains power bi

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

Total Sales Amount = SUM(SalesTable[Sales Amount])
power bi if like

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“.

if condition in power bi

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"
    )
)
pbi if statement

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.
See also  Power Query Create Table in Power BI + Examples

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.

how to use if in power bi

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

power bi if statement

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

power bi if

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.

if contains in power bi

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.

dax if contains

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

power bi if and statement

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"
    )
)
if dax powerbi

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.

dax if statement

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.

Power BI If Blank Then Put Value

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])
Power BI  How to Replace Blank with Text

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.
See also  How to Center Values in Power BI Table?

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

Power BI DAX Function ISBlank Function

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.

power query if

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.

if statement power query

2. Under the Home tab, click Transform data.

if statement in power query

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

power query if then

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

if and power query

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.

power query if number

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

power query if statement multiple criteria

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.

if statement power bi

Follow the steps below:

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

power bi custom column if statement

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.

power bi if cell contains text then

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")
power bi if multiple conditions

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.
See also  How to merge columns in Power Query

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

IF Between Two Values Power BI

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.

If text column CONTAINS specified value Power BI

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

power bi if cell contains text then

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"))
Check if a text contains a specific value in DAX

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.

powerbi if statement

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

Also, you may like:

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
>