Microsoft Office Tutorials and References
In Depth Information
FIguRE 10-7 The PriceBand table in PowerPivot.
Because you cannot rely on standard relationships, you need to find a different way to relate
the Fact_ResellerSales table to the PriceBands table. An interesting solution is to define a
calculated column in the Fact_ResellerSales table by using a DAX formula that computes, for
each single line of the sales, the band. You might want to write such a DAX formula:
= MAXX( FILTER( PriceBands,
FactResellerSales[DiscountedPrice] >= PriceBands[MinPrice]
&& FactResellerSales[DiscountedPrice] < PriceBands[MaxPrice] ),
Basically, you filter the PriceBands table searching for the only row that contains the correct
band name. FILTER produces a table with only one row, yet it is a table, and because you need
to convert the table to a single value, you use the MAXX function to perform this final step.
Unfortunately, this formula does not work because of a limitation of the MAXX function (and
the MINX too, in case you are searching for a workaround). Consistent with what happens in
Excel, those aggregation functions are designed to work on numbers and dates only, and they
raise an error if you try, as in this case, to get the max value of a string column.
Nevertheless, because the idea seems promising, you can adopt a simple trick to make it work
anyway. You can add an integer column to the original PriceBands table and name it BandCode.
With this small modification, the new PriceBand table looks like Figure 10-8.
FIguRE 10-8 The PriceBand table with the new BandCode numeric column.
Now that you have a numeric column inside the price band table, you can make the previous
formula search for the MAXX of BandCode instead of PriceBand. Because BandCode is numeric, the
MAXX function works fine and provides the only code that represents the price band. Clearly, your
calculated column now contains a numeric code, not the description of the band, but now you can
use the standard relationships of PowerPivot to create a relationship between that code and the
price band table, as you can see in Figure 10-9.