Power BI DAX Logical functions [7 Examples]

In this Power BI Tutorial, we will discuss the Power BI DAX logical functions. Such as:

• Power BI DAX Logical functions
• List of available Power BI Logical functions
• Examples of Power BI Logical functions

DAX Logical function in Power BI

In Power BI, one of the most important functions is the Logical function. It returns a result as True or False. It tests if the situation matches our condition then it returns true values otherwise it returns false.

List of Power BI Logical functions

Here is the list of logical functions available in Power BI that we can use:

Functions	Description	Syntax
IF	Checks whether the condition is met, and returns one value if TRUE, otherwise it returns FALSE.	IF(<Logical test>,ReturnIfTrue, ReturnIfFalse)
OR	It returns TRUE if any arguments are TRUE, and returns FALSE if all arguments are FALSE.	OR(Logical1, Logical2)
AND	It checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE.	AND(Logical1,Logical2)
NOT	It changes FALSE to TRUE, TRUE to FALSE.	NOT(<Logical>)
TRUE	It returns the logical value TRUE.	TRUE()
FALSE	It returns the logical value FALSE.	FALSE()
SWITCH	It returns different results depending on the value of an expression.	SWITCH(Expression, Value1, Result1,…,[Else])
IF.EAGER	It checks whether the condition is met, and returns one value if TRUE, and another value if FALSE. Uses an eager execution plan.	IF.EAGER(LogicalTest,ResultIfTrue,[ResultIfFalse])
IFERROR	It returns value_if_error if the first expression is an error and the value of an expression itself otherwise.	IFERROR(Value, ValueIfError)
COALESCE	It returns the first argument that does not evaluate to a blank value. If all arguments evaluate blank values, BLANK is returned.	COALESCE(Value1 , Value2 1, …)

Read Power bi calculated column vs measure

Examples of Power BI Logical functions

Example-1: Using IF function

To implement this function, here we have created a simple Transaction table having Users’ names and their amounts.

Now we will create a measure in Power BI by using the IF function, that will show us if the amount is greater than 500, then the result will be Up otherwise it will show Down.

Up/Down = IF(SUM('Transaction'[Amount]) > 500,"Up","Down")

This is how to use an IF function in Power BI.

Example-2: Using NOT function

Again we are going to use this sample table to execute the NOT function using in DAX.

Here we will create a Power BI measure that will calculate if the amount greater than 500 then it will show the result according to NOT function.

Not_function = IF([Total amount] > 500, NOT(FALSE),NOT(TRUE))

This is how a NOT function works in DAX example.

Read Power BI DAX SUM and SUMX function – How to use

Example-3: Using OR function

Now we will see how an OR function works on Power BI. For this, we have created a simple table having some cities names, regions names, sales like this:

Here, we will create a measure that will filter our table with fields having only NewYork or South. OR function represented as ||.

Measure_ = CALCULATE(SUM('Table (2)'[Sales]),FILTER('Table (2)', 'Table (2)'[City] ="NewYork" || 'Table (2)'[Region] = "South"))  

This is how an OR function works on Power BI.

Read Countif function in Power BI Measure

Example-4: Using AND function

Here we are going to use this same example to implement this function. So we will create a measure that will create a measure that will filter our table with fields having only NewYork and North. AND function represented as &&.

Measure_ = CALCULATE(SUM('Table (2)'[Sales]),FILTER('Table (2)', 'Table (2)'[City] ="NewYork" && 'Table (2)'[Region] = "North"))  

Example-5: Using of TRUE()/FALSE() function

Now we will see how to use TRUE() and FALSE() functions in DAX. So for this example we are going to use this previous table(cities and sales).

_Measure_ = IF(SUM('Table (2)'[Sales]) > 3000, TRUE(), FALSE() ) 

It will return if the sales is greater than 3000 then it will return TRUE otherwise it will return FALSE.

This is how to use DAX TRUE() & FALSE() in Power BI.

Read Power BI Measure Filter

Example- 6: Using SWITCH() function

Let’s create a table having some cities name like below.

Then we will create a measure that will change the color according to switch conditions.

switch_example = SWITCH(SELECTEDVALUE('Table (2)'[City]),
"Alska","Red",
"Boston", "Green",
"NewYork", "Orange",
"White"
)

To apply these background according to our conditions, go to Format panel > Conditional formatting > turn on Background color.

Then select the measure to apply on the city like this:

When we will Press OK, we can see our table’s value will change according to our condition.

Also, we can use SWITCH() function to calculate the numerical value. For this, here we are going to use our previous table having users and their amount.

Now we will create a measure that will calculate the bonus such as if the amount is less than 500, then it will multiply to 0.50, similarly if the amount is less than 700, then it will be multiplied to 0.75 otherwise it will multiply to 100.

%_interest = SWITCH(TRUE(), [Total amount] < 500, [Total amount]* 0.50, [Total amount] < 750,[Total amount]*0.75, [Total amount]*100)

This is how we can use SWITCH() function in Power BI.

Read Power bi measure switch statement with examples

Example-7: Using COALESCE() in DAX.

Let’s take an example to implement this function. Here we have created a table having some users name and their amount with blank fields.

Now we will create a measure that will show the sum of all amount or 0. This can be used to convert BLANK value of total amount to 0.

Measure2 = COALESCE(SUM('Table (3)'[Amount]),0)

This is how we can use COALESCE() function in DAX.

Related Power BI tutorials:

• How to get selected value from Slicer in Power BI
• Power BI Date Function with Examples
• How to create a filter in Power bi
• Power bi show items with no data
• Power bi group by date range
• Power BI MAX and MIN function with Examples

Conclusion

From this Power BI Tutorial, we learned the below topics:

• What is Power BI DAX Logical functions?
• What are the various functions of Power BI Logical functions with their syntax?
• What are the examples of Logical functions in Power BI?