Introduction
Logical functions allow us to introduce decision making, these functions are used to check whether the condition is true or false and are also used to combine multiple conditions together.
Logical function is one the most useful expression of DAX in Power BI. I have already explained some of DAX function categories in my previous article for example- ‘Date and Time’ functions and ‘Time Intelligence’ function. So, here in this article you will learn about ‘Logical’ functions and how to use them.
I am going to explain nine types of logical functions, so let's start.
Description of the Logical Function
IF
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.
Syntax- IF(LogicalTest, ResultIfTrue, [ResultIfFalse])
LogicalTest- Any expression that can be evaluated to TRUE or FALSE
ResultIfTrue - It will be returned if logical test is TRUE.
ResultIfFalse-If logical test is FALSE then it will be returned.
Ex.
AND
Check whether all arguments are TRUE, and returns TRUE if all arguments are TRUE.
Syntax- AND(logical1, Logical2)
Logical1- The Logical value that you want to check.
Logical2- This is also a logical value that you want to check.
Ex.
OR
Returns TRUE if any of the argumenta are TRUE, and returns false if all arguments are FALSE.
Syntax- OR (Logical1, Logical2)
Logical1 - The logic value that you want to check.
Logical2 - The logic value that you want to check.
Ex.
NOT
Changes FALSE to TRUE, or TRUE to FALSE.
Syntax- NOT (CalculatedColumn)
Ex.- To test the use of ‘NOT’ function you can first create a calculated column1 by click on ‘new column’ in ‘Modeling tab like below image.
It returned false as all card numbers are greater than 1000. Now again create a new column column2 like column2= Not(creditcard[column1]) where creditcard is my table. It will return the opposite result of column1. If column1 returns TRUE then it will return FALSE. But in our case column1 returns FALSE in the above imgae so column2 will return TRUE. See below image.
TRUE
Return the logical value TRUE always.
Syntax-TRUE()
Ex.
FALSE- Return the Logical value FALSE always.
Syntax- FALSE().
Ex.
COALESCE
Returns the first argument that does not evaluate to BLANK. If all arguments evaluate to BLANK, BLANK is returned.
Syntax- COALESCE(Value1, Value2[,Value3……])
Value1- Any expression that returns the scalar value.
Ex.
IFERROR
Returns value_if_error if the first expression is an error and the value of the expression itself otherwise.
Syntax- IFERROR(Value, ValueIfError)
Value- Any expression or value
ValueIfError- Any expression or value
Ex.
In the below example the first value will give an error so, this function will return the second value.
SWITCH
Returns different results depending on the value of an expression.
Syntax- SWITCH(Expression. Value1, Result1, …., [Else])
Expression- Any expression that returns a single scalar value, where it is to be evaluated multiple times.
Value- A constant value to be matched with the result of expression.
Result-Any expression to be evaluated if the result of expression matches with the value.
Else- Any expression to be evaluated if the result of expression does not match with value.
Ex.
In the below example ‘ExpMonth’ is a column of table. Below is the table visualization of the resultant column.
Summary
These are some ‘Logical’ functions that I explained above with example. Hope you understand the simple description of these functions. I will explain other categories of DAX functions in my upcoming articles. Thanks for reading.