Microsoft Office Tutorials and References

In Depth Information

column is always considered 0. Empty cells are never considered in the calculation. So

even if these functions can be used on non-numeric columns without retuning an error,

their results are not always the same as Excel because there is no automatic conversion to

numbers for text columns. These functions are named AVERAGEA, COUNTA, MINA, and

MAXA
,
and you can see in Figure 3-17 an example of their usage in measures operating

on a TRUE/FALSE column of the sample table shown in the same worksheet: the table is

used as a linked table in PowerPivot, and the lower part of the picture is a PivotTable based

on that PowerPivot data.

FIguRE 3-17
TRUE/FALSE are evaluated as 1/0 in A-suffixed statistical functions.

Despite the same name of statistical functions, the difference in the way they are used in

DAX and Excel exists because in PowerPivot a column has a type and its type determines the

behavior of aggregation functions. Excel handles a type for each cell, whereas PowerPivot

handles a type for each column. PowerPivot deals with data in tabular form (technically it is

called relational data) with well-defined types for each column, whereas Excel formulas work

on heterogeneous cell values, without well-defined types. If a column in PowerPivot is of a

number type, all the values can be only numbers or empty cells. If a column is of a text type,

it is always 0 for these functions, even if the text can be converted to a number, whereas in

Excel the value is considered a number on a cell-by-cell basis. For these reasons, these DAX

functions are not very useful for Text type columns.

Figure 3-18 shows a comparison of the calculation made by the same functions in Excel and

PowerPivot, using the same data you can see in the table on the left of the worksheet. The

text value N/A in the Quantity column in Excel is considered 0 by calculations of AVERAGEA,

MINA, MAXA, and COUNTA made by Excel 2010, as you can see in the second row of the

worksheet. However, the calculation of the same measure in PowerPivot considers the value

to be 0 for any cell (see the fifth row) because the whole Quantity column is imported as

Text in the corresponding PowerPivot table.