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] ),

FILTER( PriceBands,

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.