Tennick A. Practical PowerPivot & DAX Formulas for Excel 2010
Подождите немного. Документ загружается.
214 Practical PowerPivot & DAX Formulas for Excel 2010
symbol (||) means OR. If you compare the second and third columns, you can see that
all Meat/Poultry products that cost more than 30 are discontinued. RELATEDTABLE()
follows later in this chapter. SUMX() is mentioned in the next chapter (Chapter 7) and
in a later chapter on Math & Trig functions (Chapter 11).
IF()
The IF() function tests a condition. If the condition is true, then the first value is
returned; otherwise, the second value is returned. Both return values must be the same
data type—the third example here results in an error. You can nest the IF() function to
implement complex branching.
IF() is a Logical function. The examples are calculated columns on the Customers table.
Syntax
=IF(ISBLANK(Customers[Region]),FALSE(),TRUE())
=IF(ISBLANK(Customers[Region]),
IF(Customers[City]="London","London",Customers[Country]),
Customers[Region])
=IF(ISBLANK(Customers[Region]),FALSE(),Customers[Region])
Result
Analysis
The second calculated column shows the Region, if there is one. If not, it shows the
Country—apart from the city of London, where the city name is used instead of the
country name. SSAS developers may recognize a ragged hierarchy here.
Chapter 6: Fundamental Functions 215
IFERROR()
Like the IF() function, the IFERROR() function returns one of two values. Also,
like the IF() function, the two values must be of the same data type—otherwise,
IFERROR() generates an error itself. Unlike IF(), the first of the two return values
is itself a test. If the first value is going to result in an error, then the second value is
returned instead.
IFERROR() is a Logical function. The examples are calculated columns on the
Products table.
Syntax
=Products[UnitsInStock]/Products[UnitsOnOrder]
=IFERROR(Products[UnitsInStock]/Products[UnitsOnOrder],0)
Result
Analysis
The first calculated column is going to give one of two possible errors for certain
rows. Dividing a zero by zero gives NaN (not a number), while dividing a non-zero by
zero results in Infinity. IFERROR() is a convenient shorthand for the IF() function
together with the ISERROR() function. The latter is shown later in this chapter.
ISBLANK()
ISBLANK() tests for blank values. We met it earlier, when looking at the BLANK()
Text function.
ISBLANK() is an Information function. The example is a calculated column on the
Customers table.
216 Practical PowerPivot & DAX Formulas for Excel 2010
Syntax
=IF(ISBLANK(Customers[Region]),BLANK() & "No region","Region")
Result
Analysis
ISBLANK() returns either TRUE or FALSE. Here, it’s combined with the IF()
function. Please note that the concatenation of a string with BLANK() does not give a
blank.
ISERROR()
All of the IS() family of functions return TRUE or FALSE. They are often used in
conjunction with IF(). Here, we are testing for an error condition.
ISERROR() is an Information function. The examples are calculated columns on the
Products table.
Syntax
=Products[UnitsInStock]/Products[UnitsOnOrder]
=ISERROR(Products[UnitsInStock]/Products[UnitsOnOrder])
=IF(ISERROR(Products[UnitsInStock]/Products[UnitsOnOrder]),0,
Products[UnitsInStock]/Products[UnitsOnOrder])
Chapter 6: Fundamental Functions 217
Result
Analysis
The third example combines ISERROR() with IF(). You might want to compare this
to the IFERROR() function. As a reminder, NaN means “not a number.”
ISLOGICAL()
ISLOGICAL() is another IS() or Information function. It tests for a TRUE or
FALSE value and returns TRUE or FALSE.
ISLOGICAL() is an Information function. The example is a calculated column on
the Products table.
Syntax
=ISLOGICAL(Products[Discontinued])
Result
218 Practical PowerPivot & DAX Formulas for Excel 2010
Analysis
The Discontinued column in the Products table has a data type of TRUE/FALSE.
ISNONTEXT()
ISNONTEXT() has its converse in ISTEXT(). Please be careful with ISNONTEXT()—
if it returns TRUE, it does not necessarily mean the column contains numbers. If
the column has a data type of TRUE/FALSE or contains only blanks, for example,
ISNONTEXT() will show TRUE. If you want to test specifically for a number, use
ISNUMBER() instead.
ISNONTEXT() is an Information function. The example is a calculated column on
the Products table.
Syntax
=ISNONTEXT(Products[UnitPrice])
Result
Analysis
The UnitPrice column in the Products table has a data type of Currency. You may
want to test the data type of a column before attempting manipulation of the data. The
Math & Trig functions, for example, don’t operate on text values.
Chapter 6: Fundamental Functions 219
ISNUMBER()
ISNUMBER() checks whether a column contains numeric data.
ISNUMBER() is an Information function. The example is a calculated column on the
Products table.
Syntax
=ISNUMBER(Products[UnitPrice])
Result
Analysis
Although the UnitPrice column in the Products table has a data type of Currency, it
still qualifies as a numeric column.
ISTEXT()
This is the last of the IS() or Information functions. Again, it simply returns TRUE
or FALSE.
ISTEXT() is an Information function. The example is a calculated column on the
Products table.
Syntax
=ISTEXT(Products[UnitPrice])
220 Practical PowerPivot & DAX Formulas for Excel 2010
Result
Analysis
This is a useful test to perform before trying to apply some of the Text functions to the
values in a column.
NOT()
NOT() changes TRUE to FALSE and the reverse.
NOT() is a Logical function. The examples are calculated columns on the Products
table.
Syntax
=IF(Products[UnitPrice] < 50, "Cheap","Expensive")
=IF(NOT(Products[UnitPrice] > 50), "Cheap","Expensive")
Result
Chapter 6: Fundamental Functions 221
Analysis
Mishi Kobe Niku is quite pricey!
OR()
OR(), as opposed to AND(), will result in TRUE if either of the two conditions is true.
AND() only returns TRUE if both conditions are true.
OR() is a Logical function. The examples are calculated columns on any table.
Syntax
=OR(1=1,2=3)
=1=1 || 2=2 || 3=4
Result
Analysis
OR() accepts a maximum of two tests, which is different from the equivalent Excel
OR() function. If you want more, then you can nest an OR() within an OR()—or you
can use the double pipe (||) symbol, as shown in the second example.
RELATED()
RELATED() is one of the most popular DAX functions. It’s going to help you
denormalize your data and make browsing pivot tables so much easier and more
intuitive. RELATED() was discussed in some detail in Chapter 4.
222 Practical PowerPivot & DAX Formulas for Excel 2010
RELATED() is a Filter function. The first two examples are calculated columns on
the Orders table. The third example is a calculated column on the Order Details table,
the result of which is not shown in the screenshot.
Syntax
=RELATED(Employees[LastName])
=RELATED('Order Details'[Quantity])
=RELATED(Employees[LastName])
Result
Analysis
The first example returns the employee responsible for a particular order. It’s getting
data from another table. In order for this to work, you must have defined a relationship
between the two tables involved. Setting up relationships was examined in Chapter 2
and in Chapter 3. The RELATED() function must appear in the table on the many side
of a relationship—it’s doing a lookup of a value. The second example fails because, this
time, the Orders table is on the one side of the relationship. The third example will
work—it’s jumping tables. The starting point is the Order Details table. This is related
to the Orders table, which, in turn, is related to the Employees table. The net effect is to
show the employee responsible for each order line.
RELATEDTABLE()
RELATED() must appear in a table on the many side of a relationship. RELATEDTABLE()
can appear in either the many-side or the one-side table in a relationship. Generally, it is
more useful on the one side. As it returns a table, it can’t be used directly. Instead, it’s fed
Chapter 6: Fundamental Functions 223
into another DAX function that accepts a table as a parameter. The examples here pass
the rows returned by RELATEDTABLE() into the COUNTROWS() function.
RELATEDTABLE() is a Filter function. The first example is a calculated column on
the Employees table. The second example is a calculated column on the Products table.
Only the result from the first example is shown in the screenshot.
Syntax
=COUNTROWS(RELATEDTABLE(Orders))
=COUNTROWS(RELATEDTABLE(Employees))
Result
Analysis
The first example is the more useful. The RELATEDTABLE() function is in a calculated
column on the one side of a direct relationship. The outcome is a count of the number
of orders for each employee. The second example (result not in screenshot) shows the
number of employees responsible for each order line. COUNTROWS() is covered in the
next chapter.
TRUE()
The function TRUE() returns the value TRUE.
TRUE() is a Logical function. The example is a calculated column on the Customers
table.