Power BI if statement using measure and Calculate

Power bi “if statement” is straightforward to implement in DAX. It works the same as if-else in SQL. The syntax of if statement in dax is

IF(logical_test,value_if_true, value_if_false)

The first parameter of if statement in power bi is any expression that can return true or false output.

If the output of the logical_test is true, then it displays the second parameter, and if it is false, then view the third parameter.

It is essential to remember that both value_if_true and value_if_false should have the same data type; otherwise, it will throw an error.

We will see this behavior later in this post.

Let’s demonstrate if statement in power bi using an example, as you can see below.

student marks table for the demonstration of if statement in power bi — Student marks table

You can see the student “marks” table with different columns. We try to create one custom column named “Groups” with the following dax expression.

Groups = IF(Marks[Subjects] IN {"Computer","Math", "Physics"} , "Group-1","Group-0")

Assign Group-1 for “Computer”, “Math” and “Physics” subjects and Group-0 for all others.

If the result of logical condition IF(Marks[Subjects] IN {“Computer”,”Math”, “Physics”} is true then it will display Group-1 otherwise Group-0.

DAX nested if statement in Power BI

Till now, we have seen the simple implementation of if statement in dax.

Let’s move to the power bi nested if statement implementation. We will have the same data table that you have seen above.

We will write one dax expression to calculate “Grade” as a new calculated column.

Grade = IF(Marks[Obtained Marks] < 60,"C",IF(Marks[Obtained Marks] < 70,"B","A"))

In this dax formula, we have used two if statements. First, we are evaluating if the obtained marks column has a value of less than 60. It will return grade C. If the criteria return false, then we are writing again one more if statement that evaluates if “obtained marks” are less than 70, then it shows grade B otherwise grade A as you can see below.

As we had discussed earlier, the data type of “true” and “false” in the if statement should be the same; otherwise, it will throw an error.

Let’s change the formula and replace “A” with an integer value.

Grade = IF(Marks[Obtained Marks] < 60,"C",IF(Marks[Obtained Marks] < 70,"B",2))

Now in this formula, we have different data types (text and integer) for “true” and “false” values. We will get the following error

“Expressions that yield variant data-type cannot be used to define calculated columns.“

So be careful when using data types for “true” and “false” values.

If statement with logical operators in DAX

We can use if statement with logical operators to solve complex business problems.

Let’s write a dax expression to create a new calculated column.

Subject Groups = IF(Marks[Obtained Marks] > 65 && (Marks[Subjects] = "Computer" || Marks[Subjects] = "Math" || Marks[Subjects] = "Physics"),"CS Group","Non CS Group")

This formula states if the “Obtained marks” is greater than 65 and subjects are equal to “Computer”, “Math” or “Physics” then it will show “CS Group” otherwise it will show “Non CS Group”.

If statement with logical operators in DAX

We can use a double pipe operator or “OR” operator in the if statement. We have a limitation when we use “OR operator.”

Let’s write a dax formula to elaborate on it.

Logical flavors = IF(OR(Marks[Subjects] = "Math",Marks[Subjects] = "Physics",Marks[Subjects] = "Computer"),"Group-1","Group-0")

This formula will produce an error “Too many arguments were passed to the OR function. The maximum argument count for the function is 2.” With the OR function, we can only pass two parameters, but if we need more than two conditions, then we have to use a double pipe operator as below.

Logical flavors = IF(Marks[Subjects] = “Math”|| Marks[Subjects] = “Physics” || Marks[Subjects] = “Computer”,”Group-1″,”Group-0″)

In the same way you can use AND and && operators.

Power BI switch function in dax

Power BI switch function is convenient to perform logical tests. The syntax of the switch function in dax is

SWITCH(expression, value, result[, value, result]…[, else])

The first parameter of the switch function is an expression. You can give an expression that can produce scalar value.

The second parameter is a “value.” that needs to match the result of the provided expression.

The third parameter is a “result.” If the “value” parameter is matched to the result of an expression, then it displays the “result” otherwise, it goes for other matches or in the else part.

Let’s write an expression for switch function in power bi.

 Abbreviation = 
SWITCH(Marks[Subjects],
"Physics","Py",
"Chemistry","Che",
"Math","MH",
"Computer","CS",
"Bio","Bio","Unkown"
)

We have provided subjects as an expression to the switch function. After this, we are checking the values in the subject column. If the value is equal to “Physics,” then it will display “Py.” In the same way, it will row by row and evaluate switch function for every subject. If no subject matches, then it will go in the else part and display “Unkown.”

Switch function in DAX with True()

Let’s make switch function a little more complex. We can write a dax formula to display marks for computer and math. The criteria are that if the subject is math and display its obtained marks as it. If the subject is a computer, then first, it checks if the midterm marks for the computer are greater than 15 then displays its obtained marks.

 CS marks = SWITCH(TRUE(),
                    Marks[Subjects] = "Math",Marks[Obtained Marks],
                    AND(Marks[Subjects] = "Computer",Marks[Mid term Marks]> 15),Marks[Obtained Marks],0)

Here we have provided the first parameter is “True().” It means, in any case, the first expression is true, and it goes for checking multiple conditions.

The best part is we can combine conditions using different operators, and in the result parameter, we can use any column or measure.

if statement in measure power bi

A lot of people struggling to use if statement in dax measures. It is quite easy to use if statement in dax power bi measures.

Let’s use countif to get those subjects for each student where obtained marks for each subject is greater than 60.

We need to write the following dax expression to accomplish this task.

Subject count = COUNTX(FILTER(Marks,Marks[Obtained Marks] > 60),Marks[Subjects])

Here we have used countx function and pass the first parameter as a filter function. This filter function will return a table where obtained marks are greater than 60. Countx function will iterate a table that is returned by the filter function and apply counting on the subject column.

In the end, we will drag students and the “Subject count” measure on the report, as you can see below.

Now we need further drill down and try to get female students only. We need all the female students with those subjects where obtained marks are greater than 60.

To accomplish this task, we need to use power bi calculate function with filter function.

Female students = CALCULATE([Subject count],Students[Gender] = "F")

We have provided [Subject count] measure as an expression to calculate function and Student gender = “F” as a filter. This will give us “subject count” only for female students, as you can see below.

Power BI calculate to filter female students

Finally, we have to use if statement in dax measure, as you can see below.

Subject > 2 = IF([Subject count] > 3,[Female students],BLANK())

We have provided criteria to if function as “subject count” measure. If the subject count is greater than 3, then it will return another measure called “Female students” otherwise, it will return a blank.