PowerPivot: DAX: Logical Functions

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.

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"
     , ""
     )

Back to top


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

Back to top


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.

Back to top


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)

Back to top


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.

Back to top


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"
   , ""
   )

Back to top


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

Back to top


See Also


External Links

Leave a Comment
  • Please add 1 and 1 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • 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.
Comments
  • Ed Price MSFT edited Original. Comment: Added DAX link at top.

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

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

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

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

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

  • gracias por el aporte

Page 1 of 1 (7 items)