Microsoft Office Tutorials and References
In Depth Information
Now you are ready to study the complex part of the model—that is, the DAX formula that
makes the computation work. PowerPivot does not handle many-to-many relationships by
itself. It needs some help from you to be able to correctly query a data model that contains
many-to-many relationships. You need to instruct PowerPivot to follow the relationship in
the correct way to provide the result you want.
To make this model work, you need to take these two steps:
■ You need to get the freight cost for a specific order and courier. The PriceList table
contains two different prices for each order (one for each courier), although you
want to get only one.
■ You need to summarize this computed value by summing it up.
It should be clear that you cannot define calculated columns for this data model because a
calculated column does not inherit the context filter imposed by the PivotTable. Moreover,
many-to-many relationships always define nonadditive measures. So you need to work with
To help you understand the final formula, we are going to refine it step by step. You can start
defining a first measure, following the same example you learned with banding, as follows:
CalculatedFreight = VALUES( PriceList[Freight] )
The VALUES function returns a table that contains all the distinct values of the PriceList[Freight].
Clearly, this formula returns all the possible values of the freight and not a specific one for a
single courier and order. Nevertheless, if PowerPivot is forced to evaluate the formula in the
context of a single order and a single courier, the chain of relationships makes the formula
return a single value—that is, the freight charged for the order by the courier.
If you try to add this measure to a PivotTable, without solving the multiple values, you get an
error because the formula returns multiple values. So the next step is to find a suitable filter
context that makes the VALUES formula return a singleton.
To evaluate the formula in a context that restricts the evaluation to a single order, you can use
the SUMX function. If you make SUMX iterate over the Orders table and provide the measure
CalculatedFreight as the expression, the measure is evaluated once per order, in a context that
filters a single order. So you can define a new measure as this:
CalculatedOrderFreightFirstTrial = SUMX( Orders, PriceList[CalculatedFreight] )
The SUMX formula iterates over all the orders and, for each one, calculates the CalculatedFreight
measure we previously defined. However, for each single order, the value of CalculatedFreight is
not yet unique, so the formula still does not work as we want. Nevertheless, if the formula is
computed in a PivotTable that filters a single courier, the CalculatedFreight returns a single value and
can be summed up. If more than one courier was selected, the formula would return a
meaningless aggregate for more than one courier, but it would not produce errors.