Microsoft Office Tutorials and References

In Depth Information

There is a last set of statistical functions that can apply an expression to each row of a table and

then operate an aggregation on that expression. This set of functions is very useful, especially

when you want to make calculations using columns of different related tables. For example,

if a Sales table contains all the sales transactions and a related Product table contains all the

information about a product, including its cost, you might calculate the total internal cost of a

sales transaction by defining a measure with this expression:

[Cost] = SUMX( Sales, Sales[Quantity] * RELATED( Product[StandardCost] ) )

This function calculates the product of Quantity (from Sales table) and StandardCost of the

sold product (from the related Product table) for each row in the Sales table, and it returns

the sum of all these calculated values. In Figure 3-20, you can see an example of this

calculation in the Cost measure of the PivotTable.

FIguRE 3-20
Using SUMX to calculate standard cost of products sold.

Generally speaking, all the aggregation functions ending with an
X
suffix behave this way:

they calculate an expression (the second parameter) for each of the rows of a table (the first

parameter) and return a result obtained by the corresponding aggregation function (sum,

min, max or count) applied to the result of those calculations. We explain this behavior further

in Chapter 6, “Evaluation Context and CALCULATE”; evaluation context is important for

understanding how this calculation works. The
X
suffixed functions available are SUMX, AVERAGEX,

COUNTX, COUNTAX, MINX, and MAXX.

Logical Functions

Sometime you might need to build a logical condition in an expression—for example, to

implement different calculations depending on the value of a column or to intercept an

error condition. In these cases, you can use one of the logical functions in DAX. You have

already seen in the previous section, “Handling Errors in DAX Expressions,” the two most

important functions of this group, which are IF and IFERROR. In Table A-2 of the Appendix,

you can see the list of all of these functions (which are AND, FALSE, IF, IFERROR, NOT, TRUE,

and OR) and their syntax. In Figure 3-21, you can also see an example of using the IFERROR