IF

How to Use the Power BI DAX function IF

Syntax of the IF Function

The syntax of the IF function in DAX is as follows:


IF(LogicalTest, [ValueIfTrue], [ValueIfFalse])


- LogicalTest: this is the condition that you want to evaluate. It can be a Boolean expression or a comparison operation.

- ValueIfTrue: this is the value that will be returned if the LogicalTest is true.

- ValueIfFalse: this is the value that will be returned if the LogicalTest is false.

Example of Using the IF Function

Let's say we have a table that contains the sales data for a company. We want to create a calculated column that will categorize the sales based on their amount. If the sales are greater than $10,000, we want to categorize them as "High", otherwise, we want to categorize them as "Low".

To achieve this, we can use the IF function as follows:


Sales Category = IF(Sales[Amount] ❱ 10000, “High”, “Low”)


In this example, the LogicalTest is `Sales[Amount] ❱ 10000`, which will return true if the sales amount is greater than $10,000. If the LogicalTest is true, the ValueIfTrue will be "High", otherwise, the ValueIfFalse will be "Low". The result will be a new column called "Sales Category" that contains the categories for each sale.

Nested IF Functions

The IF function can also be nested to create more complex calculations. For example, let's say we want to categorize the sales based on their amount and their region. If the sales are greater than $10,000 and they are from the "West" region, we want to categorize them as "High West", otherwise, we want to categorize them based on their amount.

To achieve this, we can nest the IF functions as follows:


Sales Category = IF(Sales[Amount] ❱ 10000, IF(Sales[Region] = “West”, “High West”, “High”), IF(Sales[Amount] ❱ 5000, “Medium”, “Low”))


In this example, we have three IF functions nested together. The first IF function evaluates whether the sales amount is greater than $10,000. If it is, the second IF function will evaluate whether the sales are from the "West" region. If they are, the ValueIfTrue will be "High West", otherwise, the ValueIfFalse will be "High". If the sales amount is less than or equal to $10,000, the third IF function will evaluate whether the sales amount is greater than $5,000. If it is, the ValueIfTrue will be "Medium", otherwise, the ValueIfFalse will be "Low". The result will be a new column called "Sales Category" that contains the categories for each sale.

The IF function in DAX is a powerful tool that allows you to create dynamic and complex calculations in Power BI. By using the syntax of the IF function, you can evaluate conditions and perform different calculations accordingly. With the ability to nest IF functions, you can create even more complex calculations that will help you gain insights into your data.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)