How to use Power BI DAX logical functions with examples? Microsoft Power BI DAX provides several logical functions, such as IF, AND, OR, NOT, IN, TRUE, FALSE, IFERROR, SWITCH, etc.
To demonstrate these logical functions of Power BI DAX, we use the data shown below. As you can see, there are 15 records in this table.
Logical functions of Power BI DAX
The following series of examples shows the list of DAX logic 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 Power BI DAX If Function syntax is
IF (Expression, True_Info, False_Info)
As you can see from the syntax above, this Power BI DAX IF function accepts three arguments: the first argument is the Boolean expression (which returns true or false). If the result of the expression is TRUE, the second argument returns; otherwise the third argument will return.
To demonstrate these DAX logic functions in Power BI, we have to use Calculated. For create a column, click on the New column option under the Home tab, or Modeling tongue.
We change the name of the column default as IfExample. As you can see in the screenshot below, while typing, Power BI IntelliSense displays the suggestions.
For the purpose of demonstrating Power BI DAX Logical Functions, we use the Sales column.
Clicking Enter or any key creates a new column. And the final Code is
IfExample = IF (EmployeeSales [Sales]> 3000, "Good", "Bad")
The Power BI DAX IF function above checks whether the Sales amount of each column is greater than 3000 or not. If true, the column returns Good; otherwise, it returns Bad.
Let me add this column to the table we created earlier. See the Create Table Report article to understand the steps required to create a table.
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 following Power BI DAX nested IF function checks whether the sales amount of each column is less than 1000 or not. If true, the column returns Very Bad; otherwise it enters 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 Power BI DAX AND function checks multiple expressions. The syntax for the Power BI DAX AND function is
AND (Condition 1, Condition 2)
As you can see from the syntax above, the Power BI DAX AND function accepts two arguments: if both conditions are True, it returns True. Otherwise, it returns False.
Let me create a column to check if the sales in each column are greater than average and the annual income is greater than 70000. If both conditions are true, then the column returns a good job. Otherwise it returns Bad Job in a column
AndSales = IF (AND (EmployeeSales [Sales]> AVERAGE (EmployeeSales [Sales]), EmployeeSales [YearlyIncome]> = 70000), "Good Job", "Bad Job")
Power BI DAX OR function
The Power BI DAX OR function is like an or statement in English, which is useful for checking multiple expressions. The syntax for the Power BI DAX OR function is
OR (Condition 1, Condition 2)
As you can see from the Power BI DAX OR function syntax above: if both conditions are False, then it returns False; otherwise, it returns True.
Let me create a column to check if sales are less than average or annual income is greater than equal to 90000. If both conditions are false, the function returns Doing Good; otherwise it returns the watchlist in one column
OrSales = IF (OR (EmployeeSales [Sales] <AVERAGE (EmployeeSales [Sales]), EmployeeSales [YearlyIncome]> = 90000), "Watchlist", "Doing Good")
Let me add the And Function and Or Function columns to the table shown below.
Power BI DAX NOT function
The NO function of Power BI DAX converts True to False and False to True. I mean, it returns the opposite result. The syntax for the Power BI DAX NOT function is
NOT (Condition)
The following statement returns False if sales are greater than 2000; otherwise, it returns True.
NotSale = NOT (IF (EmployeeSales [Sales]> 2000, "TRUE", "FALSE")
Let me add this column No function to this table
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 the sales of temporary employees. The syntax for the Power BI DAX IN function is as follows:
Column IN {field1, field2, ..., fieldN}
The following statement calculates the Sum of Sales for employees whose education is Education, Bachelor's or Master's degree. Remember, this is a measurement
SalesIN = CALCULATE (SUM (EmployeeSales [YearlyIncome]), 'EmployeeSales' [Education] IN {"Education", "Bachelors", "Masters Degree"})
Let me create a card using this measurement. See the articles Create a card and Format card to understand the steps required to create and format cards.
Power BI DAX TRUE function
The Power BI DAX TRUE function returns a logical value true. The following statement returns True if the sales are greater than the average sale; otherwise it returns False
TRUESale = IF (EmployeeSales [Sales]> AVERAGE (EmployeeSales [Sales]), TRUE (), FALSE ())
Power BI DAX FALSE function
The Power BI DAX FALSE function returns a logical false. The following code returns False if sales are less than 2500. Otherwise, it returns True
FalseSale = IF (EmployeeSales [Sales] <2500, FALSE (), TRUE ())
Let me add true function, false function result to the table report
IFERROR function of Power BI DAX
The Power BI DAX IFERROR function is very useful for handling arithmetic overflow or any other errors. It just performs the calculation and returns the result, if there is an error, it returns the value within the second argument.
The syntax for the Power BI DAX IFERROR function is
IFERROR (Calculation, Value_If_Error_Occurs)
The following statement returns 100 if an error occurs. In fact all the records throw an error because we are dividing them 0
ErrorSale = IFERROR (EmployeeSales [Sales] / 0, 100)
Let me add the result of the Iferror column to the table report
SWITCH function of Power BI DAX
The SWITCH function in Power BI DAX helps you return multiple options. For example, the Power BI DAX IF function returns True or False. However, you can use this switch case to obtain various results.
The syntax for the Power BI DAX switch function is as follows:
SWITCH (Expression, Option 1, Result 1, Option 2, Result 2, ....., ElseResult)
If the hiring month date is 1, the statement below 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 ")
Let me add this result column from Power BI DAX Switch function to this table report
