The Power BI IF function is one of the essential tools for any data analyst. Let's take a closer look at what it does, and more importantly, how to use it.
What is the Power BI If function?
The IF function on Power BI corresponds to the if function. The idea is to test a condition. If the condition is true, the IF function transmits a given value. Failing this, it indicates another value.
In concrete terms, this is what the function looks like:
IF(<logical-test>,<value-if-true>[,<value-if-false>])
The “logical test” corresponds to what you want to test.
The “value if true” corresponds to the value of the test if it is true. Here, you can specify what you want. For example: “true”, “ok”, etc.
Conversely, the “value if false” appears as soon as the condition is not met. In the same spirit, you can specify any value you like. Please note that this parameter is optional. You can also leave it blank.
This function can be translated as: if …, then …, otherwise ….
Power BI’s IF function can be used for different types of data, whether numeric or alphanumeric.
💡Related articles:
How to use the Power BI If function
There are several ways to use the IF function with Power BI, either manually or by inserting a conditional column.
Manually
To understand how to use the IF function with Power BI, let’s look at some concrete examples.
Example 1
A company wants to highlight its premium products, those costing over €1,000. In this case, the IF function would be translated as follows:
IF(product<1000 €, “premium”)
Please note that an IF function can also include other IF functions in Power BI. In the previous example, the company also wishes to classify its products into several categories: premium, medium or low cost. In this case, the IF function translates as follows:
IF(product>1000 €, “premium”,if(product>100 €, “low”, “medium”))
You can nest several IF functions one after the other. But to avoid complicating the calculation when there are too many conditions, it’s best to use the Switch function on Power Bi.
Example 2
A company wants to offer a 10% discount to customers who buy €500 worth of goods.
In this case, the IF function would translate as follows:
IF(total-spend>500 €, “0,9*total-spend”, “total-spend”)
The conditional column
You can also add a conditional column to use the Power BI IF function.
In this case, you’ll use the Power Query editor. The procedure is as follows:
- Click on “Query”, then “Modify” ;
- Select “Add a column”, then “Conditional column”;
- A new dialog box appears to set your condition if.
- New column name: this is the name you wish to assign to the column.
- Column name: select the column.
- Operator: you can select contains, greater than, less than… depending on your hypothesis.
- Value: the hypothesis to be tested.
- Output: the value if true.
- If you wish to add other conditions, you can click on “Add a rule”.
Conclusion
The Power BI If function tests a condition and displays a given value if the condition is true, or another value if it is false. It can be used with different types of data and can be used manually or by inserting a conditional column.
The IF function can be nested with other Power BI IF functions to test multiple conditions, but it is preferable to use the Switch function when there are too many conditions to avoid complicating the calculation. The IF function can be used in many different scenarios, such as displaying different values according to a condition, or creating conditional columns to filter data.
