Microsoft Office Tutorials and References
In Depth Information
FIguRE 10-9 The relationship between sales and bands.
This final step makes the full data model work fine.
If you think that you are somehow fooling PowerPivot, well, you are right. Because you cannot
make it search for a string by using MAXX, you trick it by asking it to look for a number (which
it can do very well) and then use this number to search the string through a relationship.
Clearly, this solution works but is far from being either elegant or efficient. So we look for
a third solution to the same problem that looks better and works faster too.
Banding with CALCULATE
The last solution you are going to learn for the banding problem uses the CALCULATE function.
We present it as the last solution even if—from the elegance point of view—it is the best.
Using the same data model of the previous example, you can define a new column inside the
sales table, whose formula is this:
= CALCULATE( VALUES( PriceBands[PriceBand] ),
FactResellerSales[DiscountedPrice] >= PriceBands[MinPrice]
&& FactResellerSales[DiscountedPrice] < PriceBands[MaxPrice] ) )
The logic of computation is the same as above, but this time you use, as we said, the CALCULATE
function. You perform a calculation of the values of the PriceBand column (first parameter of
CALCULATE) in a filter context specified in the second argument of the CALCULATE function, in
which you limit the items visible in the PriceBand table to the only one that satisfies the range
condition. The VALUES call is required because CALCULATE needs an aggregation function,
even if we know that the FILTER always returns one single row. Because each call to CALCULATE
returns just one string, you can safely use this formula in a column definition. The final result is
that the calculated column contains the price band of each sale and you can use that column
to slice sales.