Logical functions of Power BI DAX

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.

<>

IF (Expression, True_Info, False_Info)

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