Microsoft Office Tutorials and References

In Depth Information

Moreover, you can easily perform more complex computationsâ€”for example, at the country

level for some years, as in Figure 10-27.

FIguRE 10-27
Comparison of freights from different couriers.

This solution is very simple to implement, and if you need to analyze further, you can add

all the columns that interest you in the Orders table to get more complex and interesting

analyses.

Same Solution with CALCuLATE

Before we end this section, it is worth noting that the previous formula to compute the

freight for a single courier, might be expressed with CALCULATE, as was the case in

the banding example.

BlueYonderFreight = CALCULATE( VALUES( Couriers[Freight] ),

FILTER( Couriers,

Couriers[Country] = Orders[Country]

&& Couriers[MinWeight] <= Orders[Weight]

&& Orders[Weight] < Couriers[MaxWeight]

&& Couriers[Courier] = "BlueYonder" ) )

This formula is slightly harder to understand because it makes use of the CALCULATE

function. It basically retrieves the values of the Freight column from the Couriers table

after having applied a filter context that filters only the relevant rows. Because you use

it to define a column, this formula raises an error if the number of values returned is

more than one, providing a safer environment whenever errors are detected. The use of

CALCULATE might be slightly better also from a performance point of view. Nevertheless,

the formula is less intuitive than the previous one. It is up to you to choose the best for

your environment.