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
Search JabSto ::

Custom Search