Resources For IT Professionals
United States (English) # PowerPivot: DAX: Logical Functions

Logical Data Analysis Expressions (DAX) functions 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.

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.

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 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 is 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.

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())

Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
• SQLSophist edited Revision 24. Comment: Updated with text from DAX function reference (technet.microsoft.com/.../ee634379(v=sql.110).aspx) to read that the AND function only accepts two arguments.

• Ed Price MSFT edited Revision 18. Comment: I also elongated the DAX link at the top for consistency and to make it more obvious.

• Ed Price MSFT edited Revision 17. Comment: Removed the line break at the top.

• Ed Price MSFT edited Revision 14. Comment: Added horizontal lines.

• Ed Price MSFT edited Revision 13. Comment: Added TOC tag.

• Ed Price MSFT edited Original. Comment: Added DAX link at top.

Page 1 of 1 (6 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
• gracias por el aporte

• SQLSophist edited Revision 24. Comment: Updated with text from DAX function reference (technet.microsoft.com/.../ee634379(v=sql.110).aspx) to read that the AND function only accepts two arguments.

• Ed Price MSFT edited Revision 18. Comment: I also elongated the DAX link at the top for consistency and to make it more obvious.

• Ed Price MSFT edited Revision 17. Comment: Removed the line break at the top.

• Ed Price MSFT edited Revision 14. Comment: Added horizontal lines.

• Ed Price MSFT edited Revision 13. Comment: Added TOC tag.

• Ed Price MSFT edited Original. Comment: Added DAX link at top.

Page 1 of 1 (7 items)