Power BI: DAX: Logical Functions
Logical Data Analysis Expressions (DAX) functions to act upon an expression to return information about the values or sets in the expression. For example, you can use the IF function to check the result of an expression and create conditional results.
Read more about the following logical functions:
Original article (before wiki edits) was written by Jeannine Takaki and JuanPablo Jofre and formatted by Mary Browning, Microsoft SQL Server Technical Writers.
AND
Checks whether both arguments are TRUE, and returns TRUE if both arguments are TRUE. Otherwise returns FALSE.
Returns true or false depending on the combination of values that you test.
Syntax
AND(<logical1>,<logical2>)
**Logical1, logical2 **The logical values you want to test.
Remarks
The AND function in DAX accepts only two (2) arguments. If you need to perform an AND operation on multiple expressions, you can create a series of calculations or, better, use the AND operator (&&) to join all of them in a simpler expression.
Example
The following formula shows the syntax of the AND function:
=IF(AND( 10 > 9, -10 < -1), "All true", "One or more false")
Because both conditions passed as arguments to the ALL function are true, the formula returns "All True".
The following sample uses the AND function with nested formulas to compare two sets of calculations at the same time. For each product category, the formula determines if the current year sales and previous year sales of the Internet channel are larger than the Reseller channel for the same periods. If both conditions are true, for each category the formula returns the value, "Internet hit".
| AND function | ||||||
| Row Labels | 2001 | 2002 | 2003 | 2004 | Grand Total | |
| Bib-Shorts | ||||||
| Bike Racks | ||||||
| Bike Stands | Internet Hit |
|||||
| Bottles and Cages | Internet Hit |
|||||
| Bottom Brackets | ||||||
| Brakes | ||||||
| Caps | ||||||
| Chains | ||||||
| Cleaners | ||||||
| Cranksets | ||||||
| Derailleurs | ||||||
| Fenders | Internet Hit |
|||||
| Forks | ||||||
| Gloves | ||||||
| Handlebars | ||||||
| Headsets | ||||||
| Helmets | ||||||
| Hydration Packs | ||||||
| Jerseys | ||||||
| Lights | ||||||
| Locks | ||||||
| Mountain BIkes | ||||||
| Mountain Frames | ||||||
| Panniers | ||||||
| Pedals | ||||||
| Pumps | ||||||
| Road Bikes | ||||||
| Road Frames | ||||||
| Saddles | ||||||
| Shorts | ||||||
| Socks | ||||||
| Tights | ||||||
| Tires and Tubes | Internet Hit |
|||||
| Touring Bikes | ||||||
| Touring Frames | ||||||
| Vests | ||||||
| Wheels | ||||||
| Grand Total |
Formula: = IF( AND( SUM( 'InternetSales_USD'[SalesAmount_USD])
>SUM('ResellerSales_USD'[SalesAmount_USD])
, CALCULATE(SUM('InternetSales_USD'[SalesAmount_USD]), PREVIOUSYEAR('DateTime'[DateKey] ))
>CALCULATE(SUM('ResellerSales_USD'[SalesAmount_USD]), PREVIOUSYEAR('DateTime'[DateKey] ))
)
, "Internet Hit"
, ""
)
FALSE
Always returns the logical value FALSE.
The word FALSE is also interpreted as the logical value FALSE.
Syntax
FALSE()
Example
The formula returns the logical value FALSE when the value in the column, 'InternetSales_USD'[SalesAmount_USD], is less than or equal to 200000.
The following table shows the results when the example formula is used with 'ProductCategory'[ProductCategoryName] in Row Labels and 'DateTime'[CalendarYear] in Column Labels.
| True-False | ||||||
| Row Labels | 2001 | 2002 | 2003 | 2004 | Grand Total | |
| Accessories | FALSE |
FALSE |
TRUE |
TRUE |
FALSE |
TRUE |
| Bikes | TRUE |
TRUE |
TRUE |
TRUE |
FALSE |
TRUE |
| Clothing | FALSE |
FALSE |
FALSE |
FALSE |
FALSE |
TRUE |
| Components | FALSE |
FALSE |
FALSE |
FALSE |
FALSE |
FALSE |
FALSE |
FALSE |
FALSE |
FALSE |
FALSE |
FALSE |
|
| Grand Total | TRUE |
TRUE |
TRUE |
TRUE |
FALSE |
TRUE |
Formula: =IF(SUM('InternetSales_USD'[SalesAmount_USD]) >200000, TRUE(), false())
IF
Checks if a condition provided as the first argument is met. Returns one value if the condition is TRUE, and returns another value if the condition is FALSE.
If the value of value_if_true or value_if_false is omitted, IF treats it as an empty string value ("").
If the value referenced in the expression is a column IF returns the value that corresponds to the current row.
The IF function attempts to return a single data type in a column. Therefore, if the values returned by value_if_true and value_if_false are of different data types, the IF function will implicitly convert data types to accommodate both values in the column. For example, the formula IF(<condition>,TRUE(),0) returns a column of ones and zeros and the results can be summed, but the formula IF(<condition>,TRUE(),FALSE()) returns only logical values. For more information about implicit data type conversion, see Data Types Supported in PowerPivot Workbooks in the TechNet Library.
Returns any type of value that can be returned by an expression.
Syntax
IF(logical_test>,<value_if_true>, value_if_false)
Logical_test. Any value or expression that can be evaluated to TRUE or FALSE.
Value_if_true. The value that is returned if the logical test is TRUE. If omitted, TRUE is returned.
Value_if_false. The value that is returned if the logical test is FALSE. If omitted, FALSE is returned.
Examples
The following example uses nested IF functions that evaluate the number in the column, Calls, from the table FactCallCenter in Adventure Works DW2008R2. The function assigns a label as follows: low if the number of calls is less than 200, medium if the number of calls is less than 300 but not less than 200, and high for all other values.
=IF([Calls]<200,"low",IF([Calls]<300,"medium","high"))
The following example gets a list of cities that contain potential customers in the California area by using columns from the table ProspectiveBuyer in Adventure Works DW2008R2. Because the list is meant to plan for a campaign that will target married people or people with children at home, the condition in the IF function checks for the value of the columns [MaritalStatus] and [NumberChildrenAtHome], and outputs the city if either condition is met and if the customer is in California. Otherwise, it outputs the empty string.
=IF([StateProvinceCode]= "CA" && ([MaritalStatus] = "M" || [NumberChildrenAtHome] >1),[City])
Note that parentheses are used to control the order in which the AND (&&) and OR (||) operators are used. Also, note that no value has been specified for value_if_false. Therefore, the function returns the default, which is an empty string.
IFERROR
Evaluates an expression and returns a specified value if the expression returns an error; otherwise returns the value of the expression itself.
You can use the IFERROR function to trap and handle errors in an expression.
If value or value_if_error is an empty cell, IFERROR treats it as an empty string value ("").
The IFERROR function is based on the IF function, and uses the same error messages, but has fewer arguments. The relationship between the IFERROR function and the IF function as follows:
IFERROR(A,B) := IF(ISERROR(A), B, A)
Note that the values that are returned for A and B must be of the same data type; therefore, the column or expression used for value and the value returned for value_if_error must be the same data type.
Returns a scalar of the same type as value.
Syntax
IFERROR(value, value_if_error)
Value. Any value or expression.
Value_if_error. Any value or expression.
Example
The following example returns 9999 if the expression 25/0 evaluates to an error. If the expression returns a value other than an error, that value is passed to the invoking expression.
=IFERROR(25/0,9999)
NOT
Changes FALSE to TRUE, or TRUE to FALSE.
Returns TRUE or FALSE.
Syntax
NOT(<logical>)
Logical. A value or expression that can be evaluated to TRUE or FALSE.
Example
The following example retrieves values from the calculated column that was created to illustrate the IF function. For that example, the calculated column was named using the default name, Calculated Column1, and contains the following formula: =IF([Orders]<300,"true","false")
The formula checks the value in the column, [Orders], and returns "true" if the number of orders is under 300.
Now create a new calculated column, Calculated Column2, and type the following formula:
=NOT([CalculatedColumn1])
For each row in Calculated Column1, the values "true" and "false" are interpreted as the logical values TRUE or FALSE, and the NOT function returns the logical opposite of that value.
OR
Checks whether one of the arguments is TRUE to return TRUE. The function returns FALSE if all arguments are FALSE.
The function evaluates the arguments until the first TRUE argument and then returns TRUE.
Returns a Boolean value. The value is TRUE if any of the arguments are TRUE; the value is FALSE if all the arguments are FALSE.
Syntax
OR(<logical1>,<logical2>)
Logical1, logical2. The logical values you want to test.
Example
The following example shows how to use the OR function to obtain the sales people that belong to the Circle of Excellence. The Circle of Excellence recognizes those who have achieved more than a million dollars in Touring Bikes sales or sales of over two and a half million dollars in 2003.
| OR function | ||||||
| Row Labels | 2001 | 2002 | 2003 | 2004 | Grand Total | |
Abbas, Syed E |
||||||
Alberts, Amy E |
||||||
Ansman-Wolfe, Pamela O |
||||||
Blythe, Michael G |
Circle of Excellence |
Circle of Excellence |
Circle of Excellence |
Circle of Excellence |
Circle of Excellence |
Circle of Excellence |
Campbell, David R |
||||||
Carson, Jillian |
Circle of Excellence |
Circle of Excellence |
Circle of Excellence |
Circle of Excellence |
Circle of Excellence |
Circle of Excellence |
Ito, Shu K |
||||||
Jiang, Stephen Y |
||||||
Mensa-Annan, Tete A |
||||||
Mitchell, Linda C |
Circle of Excellence |
Circle of Excellence |
Circle of Excellence |
Circle of Excellence |
Circle of Excellence |
Circle of Excellence |
Pak, Jae B |
Circle of Excellence |
Circle of Excellence |
Circle of Excellence |
Circle of Excellence |
Circle of Excellence |
Circle of Excellence |
Reiter, Tsvi Michael |
||||||
Saraiva, José Edvaldo |
Circle of Excellence |
Circle of Excellence |
Circle of Excellence |
Circle of Excellence |
Circle of Excellence |
Circle of Excellence |
Tsoflias, Lynn N |
||||||
Valdez, Rachel B |
||||||
Vargas, Garrett R |
||||||
Varkey Chudukatil, Ranjit R |
Circle of Excellence |
|||||
| Grand Total | Circle of Excellence |
Circle of Excellence |
Circle of Excellence |
Circle of Excellence |
Circle of Excellence |
Circle of Excellence |
Formula: IF( OR( CALCULATE(SUM('ResellerSales_USD'[SalesAmount_USD]), 'ProductSubcategory'[ProductSubcategoryName]="Touring Bikes") > 1000000
, CALCULATE(SUM('ResellerSales_USD'[SalesAmount_USD]), 'DateTime'[CalendarYear]=2003) > 2500000
)
, "Circle of Excellence"
, ""
)
TRUE
Always returns the logical value TRUE.
The word TRUE is also interpreted as the logical value TRUE.
Syntax
TRUE()
Example
The formula returns the logical value TRUE when the value in the column, 'InternetSales_USD'[SalesAmount_USD], is greater than 200000.
The following table shows the results when the example formula is used in a PivotTable, with 'ProductCategory'[ProductCategoryName] in Row Labels and 'DateTime'[CalendarYear] in Column Labels.
| True-False | ||||||
| Row Labels | 2001 | 2002 | 2003 | 2004 | Grand Total | |
| Accessories | FALSE |
FALSE |
TRUE |
TRUE |
FALSE |
TRUE |
| Bikes | TRUE |
TRUE |
TRUE |
TRUE |
FALSE |
TRUE |
| Clothing | FALSE |
FALSE |
FALSE |
FALSE |
FALSE |
TRUE |
| Components | FALSE |
FALSE |
FALSE |
FALSE |
FALSE |
FALSE |
FALSE |
FALSE |
FALSE |
FALSE |
FALSE |
FALSE |
|
| Grand Total | TRUE |
TRUE |
TRUE |
TRUE |
FALSE |
TRUE |
Formula: = IF(SUM('InternetSales_USD'[SalesAmount_USD]) >200000, TRUE(), false())