Power BI DAX Logical Functions

Microsoft Power BI DAX provides various Logical Functions such as: IF Statement, AND, OR, NOT, IN, TRUE, FALSE, IFERROR, SWITCH etc. In this article we will how you, How to use Power BI DAX Logical Functions with examples.

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

Power BI DAX Logical Functions

The following series of examples will show you the list of DAX Logical Functions in Power BI

Power BI DAX IF Function

The DAX If function is used to check the given expression is True or False. The basic syntax of the Power BI DAX If Function is as shown below:

IF(Expression, True_Info, False_Info)

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

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

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 demo purpose, we will use the Sales column.

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

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

Above statement will check whether the Sales amount of each column is greater than 3000 or not. If true then column will return Good otherwise, Bad will return

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

Power BI DAX Nested IF Function

In Power BI DAX, you can use the Nested If concept. I mean, one If statement inside another. Below statement will check whether the Sales amount of each column is less than 1000 or not. If true then column will return Very Bad otherwise, it will enter into Nested If

NestedIfEx = IF(EmployeeSales[Sales] < 1000, "Very Bad",                                           
                 IF(EmployeeSales[Sales] > 3000, "Good", "Average" ))

Let me add this Nested column to this table.

Power BI DAX AND Function

The DAX AND function is used to check multiple expressions. The syntax of the Power BI DAX AND Function is as shown below:

AND(Condition 1, Condition 2)

As you can see from the above syntax, DAX AND function accepts two arguments: If both the conditions are True then it will return True otherwise, it return 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 column will return Good Job otherwise, it will return Bad Job into column

AndSales = IF(AND(EmployeeSales[Sales] > AVERAGE(EmployeeSales[Sales]),                              
               EmployeeSales[YearlyIncome] >= 70000), "Good Job", "Bad Job")

Power BI DAX OR Function

The DAX OR function is like either or statement in English, which is used to check multiple expressions. The syntax of the Power BI DAX OR Function is as shown below:

OR(Condition 1, Condition 2)

As you can see from the above syntax: If both the conditions are False then it will return False otherwise, it return 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 function will return Doing Good otherwise, it will return Watchlist into column

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

let me add both the And Function, and Or Function columns to the below shown table.

Power BI DAX NOT Function

The DAX NOT function will convert True to false, and False to True. I mean, it return opposite result. The syntax of the Power BI DAX NOT Function is as shown below:

NOT(Condition)

Below statement will return False if Sales is greater than 2000 otherwise, it will return True

NotSale = NOT(IF(EmployeeSales[Sales] > 2000, "TRUE", "FALSE")

Let me add this Not Function column to this table

Power BI DAX IN Function

The DAX IN function will restrict the clculation 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}

Below statement will 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"})

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

Power BI DAX TRUE Function

The DAX TRUE function will return logical true. Below statement will return True if Sales is greater than Average Sale otherwise, it will return False

TRUESale = IF(EmployeeSales[Sales] > AVERAGE(EmployeeSales[Sales]), TRUE(), FALSE() )

Power BI DAX FALSE Function

The DAX FALSE function will return logical false. Below statement will return False if Sales is less than 2500 otherwise, it will return True

FalseSale = IF(EmployeeSales[Sales] < 2500, FALSE(), TRUE() )

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

Power BI DAX IFERROR Function

The DAX IFERROR function is very useful to handle the arithmetic overflow, or any other errors. It simply perform calculation and return the result, if there is an error then it will return the value inside the second argument.  The syntax of the Power BI DAX IFERROR Function is as shown below:

IFERROR(Calculation, Value_If_Error_Occurs)

Below statement will return 100 if error occurs. Indeed, all the records will throw error because we are dividing them 0

ErrorSale = IFERROR(EmployeeSales[Sales]/0, 100)

Let me add Iferror column result to the table report

Power BI DAX SWITCH Function

The DAX SWITCH function helps you to return multiple options. For example, If statement will return either True or false. However, you can use this switch case to multiple results The syntax of the Power BI DAX IFERROR 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 will return 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")

let me add this Switch function result column to this table report