Microsoft Office Tutorials and References
In Depth Information
Note Actually, there is a subtle difference between the two: the expression based on the
ALLEXCEPT function operates even when there are relationships with other tables, ignoring
possible filters implicitly included by those relationships. If you want to get the same result
as ALLEXCEPT, you need to add the entire list of ALL calls—that is, you should include an
ALL call for each table related to the one you are basing your calculation on (Orders in this
example). The following use of the CALCULATE function is truly equivalent to the one you
saw before using the ALLEXCEPT function:
CALCULATE( SUM( Orders[Amount] ),
ALL( Channels ) , ALL( Cities ) ,
ALL( Orders[Channel], Orders[Color], Orders[Size],
Orders[Quantity], Orders[Price], Orders[Amount] ) )
For this reason, we recommend the use of ALLEXCEPT whenever you want to exclude almost all
of the filters from a table.
Row Context with Multiple Tables
Now, you can take a step further by adding a new related table to the PowerPivot data
model—for example, the Channels table, which has a discount percentage for each channel
type, as you can see in Figure 6-14.
FIguRE 6-14 The Channels table added to the PowerPivot model.
You might want to calculate the discounted amount for each transaction by defining a
calculated column in the Orders table. So the first idea is to define a formula this way:
Orders[Amount] * (1 – Channels[Discount])
but this produces an error, as you can see in Figure 6-15.
FIguRE 6-15 An error produced by using the Discount column from the Channels table in a calculated
column in Orders.