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

measures only.

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.