Power BI - DAX Logical Functions

---

---

In this chapter, the DAX logical functions along with their examples are carried out. It is very helpful for making major business decisions and permits users to manipulate the data swiftly.

What are DAX's Logical Functions?

The logical values will be obtained which are dependent on the logical operations applied to the specified parameters.

Scenario − The problem statement is that you have to identify whether students have passed or failed the final examination through these logical DAX functions. You need to load the student dataset into the BI desktop before executing these functions.

Let's commence with DAX logical functions one by one −

DAX AND Function

It comprises two parameters that specify the logical conditions and return false if both constraints are incorrect and vice versa.

Example − If the student's marks are greater than 40 and practical marks greater than 2 then the AND function will fill up "TRUE" values for this condition otherwise "False" value will be filled up in the new column cal.

cal = AND(Sheet1[Theory Marks]>40,Sheet1[Practical Marks]>2)

The resulting value is shown in the cal column.

What in case you have to fill data value according to your choice? By default, the AND function will return only a "True/False" value. To overcome this problem AND can used inside the IF function. IF functions can effectively work with other logical functions like AND, and OR functions.

Example − Write the expression in the formula −

cal = If(AND(Sheet1[Theory Marks]>40,Sheet1[Practical Marks]>2),"Pass", "Fail")

If both conditions inside the AND function are correct, then "Pass" will return otherwise "Fail" will retrieve.

DAX OR Function

It will return True if any of the two specified logical tests is correct otherwise it will return False.

Example − Enter the expression in the formula bar −

cal = If(OR(Sheet1[Theory Marks]>40,Sheet1[Practical Marks]>2),"Pass", "Fail")

Therefore, the above expression is being evaluated and the computed value is displayed in the cal column.

DAX NOT Function

It transforms the True entries into the False entries and vice versa.

Example − You have already computed the given expression in the fourth column cal.

cal = AND(Sheet1[Theory Marks]>40,Sheet1[Practical Marks]>2)

Switch to the "Table Tools" tab and click on the "New column" to append the new column to the existing table.

Write the expression −

c1 = NOT(Sheet1[cal]) 

Here, you can notice that the reverse value of the cal column is displayed in the c1 column.

DAX COALESCE Function

It will return the first parameter value if it does not contain a null value otherwise, it will return the second parameter value furthermore if the second parameter contains null values, then it will extract the third parameter value and if all the specified parameters contain null values, then it will retrieve the blank.

Example − Enter the given expression in the formula bar.

mn = COALESCE(Sheet1[Wr_lf_balance],Sheet1[Jb_st],Sheet1[EST])

Here, you can observe in the screenshot, that the computed result is displayed in the mn column.

DAX Switch Function

It verifies the multiple conditions and retrieves distinct outputs based on the specified function. Row calculation is not performed with the switch function. It applies to manipulating only column-based values.

Example − You may write the expression for the new column −

pr = Switch(TRUE(), Sheet1[cal]==True, "Student Pass", Sheet1[cal]==FALSE,"Student Fail")

Here, different expressions are evaluated, and the corresponding output is generated accordingly.

DAX IFERROR Function

The expression is verified and if it contains an error then it retrieves a corresponding value defined by the user. Otherwise, retrieves the expression value itself.

Example − Enter the expression −

err = IFERROR('Sheet1 (2)'[Theory Marks],0)

As you can notice in the screenshot, the "Theory Marks" column does not contain Error values. Therefore, the same data values are returned from the "Theory Marks" into in the Err column.

Another way to execute the IFERROR function is through a new measure. Navigate to the "Table Tools" and click on "New Measure" and enter the expression −

m1=IFERROR(87/0,"Infinity")

DAX Logical Functions

The remaining DAX logical functions are listed in the given table −

The implementation of DAX logical functions in Power BI Desktop is showcased in this chapter. These are very powerful functions for data modelling, to improve business performance and also decrease human errors, and are helpful for organizational growth.