Power BI DAX Logical Functions

How to use Power BI DAX Logical Functions with examples?. Microsoft Power BI DAX provides various Logical Functions such as IF Statement, AND, OR, NOT, IN, TRUE, FALSE, IFERROR, SWITCH, etc.

To demonstrate these Power BI DAX Logical functions, we use the below-shown data. As you can see, there are 15 records in this table.

Power BI DAX Logical Functions 1

Power BI DAX Logical Functions

The following series of examples shows the list of DAX Logical Functions in Power BI.

Power BI DAX IF Function

The Power BI DAX If function checks whether the given expression is True or False. The syntax of the Power BI DAX If Function is

IF(Expression, True_Info, False_Info)

As you can see from the above syntax, this Power BI DAX IF function accepts three arguments: the first argument is the Boolean expression (which returns true or false). If the expression results TRUE, then the second argument return; otherwise, the third argument will return.

To demonstrate these DAX logical functions in Power BI, we have to use Calculated. To create a column, please click on the New Column option under the Home tab, or Modeling tab.

Power BI DAX Logical Functions 2

We renamed the default column name as IfExample.  As you can see from the below screenshot, while I was typing, Power BI IntelliSense is showing the suggestions.

For the Power BI DAX Logical Functions demo purpose, we use the Sales column.

Power BI DAX Logical Functions 3

By clicking the enter or any key, a new column created. And the final Code is

IfExample = IF(EmployeeSales[Sales] > 3000, "Good", "Bad")

The above Power BI DAX IF function checks whether the Sales amount of each column is greater than 3000 or not. If true, then column returns Good; otherwise, it returns Bad.

Power BI DAX IF Functions 1

Let me add this column to the table that we created earlier. Please refer to Create Table Report article to understand the steps involved in creating a table

Power BI DAX IF Functions 5

Power BI DAX Nested IF Function

In Power BI, you can use the DAX Nested If concept. I mean, one If statement inside another. The below Power BI DAX Nested IF function checks whether the Sales amount of each column is less than 1000 or not. If true then the column returns Very Bad otherwise, it enters into Nested If

NestedIfEx = IF(EmployeeSales[Sales] < 1000, "Very Bad",                                           
                 IF(EmployeeSales[Sales] > 3000, "Good", "Average" ))
Power BI DAX IF Functions 6

Let me add this Nested column to this table.

Power BI DAX IF Functions 7

Power BI DAX AND Function

The Power BI DAX AND function check multiple expressions. The syntax of the Power BI DAX AND Function is

AND(Condition 1, Condition 2)

As you can see from the above syntax, the Power BI DAX AND function accepts two arguments: If both the conditions are True, then it returns True. Otherwise, it returns False.

Let me create a column to check whether the Sales of each column is greater than Average, and Yearly Income is greater than 70000. If both these conditions are true, then the column returns a Good Job. Otherwise, it returns Bad Job into a column

AndSales = IF(AND(EmployeeSales[Sales] > AVERAGE(EmployeeSales[Sales]),                              
               EmployeeSales[YearlyIncome] >= 70000), "Good Job", "Bad Job")
Power BI DAX and Functions 8

Power BI DAX OR Function

The Power BI DAX OR function is like either or statement in English, which is useful to check multiple expressions. The Power BI DAX OR Function syntax is

OR(Condition 1, Condition 2)

As you can see from the above Power BI DAX OR function syntax: If both the conditions are False, then it returns False; otherwise, it returns True.

Let me create a column to check whether the Sales is less than Average, or Yearly Income is greater than equal to 90000. If both these conditions are false, then the function returns Doing Good; otherwise, it returns Watchlist into a column

OrSales = IF(OR(EmployeeSales[Sales] < AVERAGE(EmployeeSales[Sales]),                              
               EmployeeSales[YearlyIncome] >= 90000), "Watchlist", "Doing Good")
Power BI DAX OR Functions 9

Let me add both the And Function and Or Function columns to the below-shown table.

Power BI DAX AND OR Functions 10

Power BI DAX NOT Function

The Power BI DAX NOT function converts True to false and False to True. I mean, it returns the opposite result. The syntax of the Power BI DAX NOT Function is

NOT(Condition)

The below statement returns False if Sales is greater than 2000; otherwise, it returns True.

NotSale = NOT(IF(EmployeeSales[Sales] > 2000, "TRUE", "FALSE")
Power BI DAX NOT Functions 11

Let me add this Not Function column to this table

Power BI DAX NOT Function 12

Power BI DAX IN Function

The Power BI DAX IN function restricts the calculation to specified columns. For example, you can calculate the sum of sales for temporary Employees. The syntax of Power BI DAX IN Function is as shown below:

Column IN {field1, field2,...,fieldN}

The below statement calculate the Sum of Sales for the employees whose education is Education, Bachelors, or Masters Degree. Remember, this is a Measure

SalesIN =  CALCULATE(SUM(EmployeeSales[YearlyIncome]) , 
        'EmployeeSales'[Education] IN {"Education", "Bachelors", "Masters Degree"})
Power BI DAX IN Function 13

Let me create a Card using this Measure. Please refer to Create a Card and Format Card articles to understand the steps involved in creating and formatting cards.

Power BI DAX IN Function 14

Power BI DAX TRUE Function

The Power BI DAX TRUE function returns logical true. The below statement returns True if Sales is greater than Average Sale; otherwise, it returns False

TRUESale = IF(EmployeeSales[Sales] > AVERAGE(EmployeeSales[Sales]), TRUE(), FALSE() )
Power BI DAX TRUE Function 15

Power BI DAX FALSE Function

The Power BI DAX FALSE function returns logical false. The below code returns False if Sales are less than 2500. Otherwise, it returns True

FalseSale = IF(EmployeeSales[Sales] < 2500, FALSE(), TRUE() )
Power BI DAX FALSE Function 16

Let me add True Function, False Function result to the table report

Power BI DAX TRUE AND FALSE Functions 17

Power BI DAX IFERROR Function

The Power BI DAX IFERROR function is very useful to handle the arithmetic overflow or any other errors. It simply performs the calculation and returns the result, if there is an error, then it returns the value inside the second argument.

The syntax of the Power BI DAX IFERROR Function is

IFERROR(Calculation, Value_If_Error_Occurs)

The below statement returns 100 if an error occurs. Indeed, all the records throw an error because we are dividing them 0

ErrorSale = IFERROR(EmployeeSales[Sales]/0, 100)
Power BI DAX IFERROR Function 18

Let me add Iferror column result to the table report

Power BI DAX IFERROR Function 19

Power BI DAX SWITCH Function

The Power BI DAX SWITCH function helps you to return multiple options. For example, Power BI DAX IF function returns either True or false. However, you can use this switch case to multiple results.

The syntax of the Power BI DAX Switch Function is as shown below:

SWITCH(Expression, Option 1, Result 1, Option 2, Result 2, ....., ElseResult)

If Month of Hire date is 1, then below statement returns January, 2 means February, 3 means march, 4 means April, 5 means May, 12 means December otherwise, Unknown.

SwitchMonth = SWITCH(MONTH(EmployeeSales[HireDate]), 1, "January", 2, "February", 3, "March", 4, "April", 5, "May", 
                 12, "December", "Unknown")
Power BI DAX SWITCH Function 20

let me add this Power BI DAX Switch function result column to this table report

Power BI DAX SWITCH Function 21

Comments are closed.