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:
|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.
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
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?
Bhawana Rathore is a Microsoft MVP (3 times in Office Apps & Services) and a passionate SharePoint Consultant, having around 10 years of IT experience in the industry, as well as in .Net technologies. She likes to share her technical expertise in EnjoySharePoint.com and SPGuides.com