Microsoft Office Tutorials and References

In Depth Information

Please note that the minimum and maximum weight ranges are different for the two couriers

shown. This makes the analysis pretty complex because you need to determine, for every

single order, which range of weight it belongs to and this will be a different computation

for each courier.

This situation is technically known as a
range lookup
and is in some ways similar to the

banding process. You need to create a relationship between two tables and the key is not

a single column that should match by exact value; instead the match occurs when the key

falls within a defined range of values. Moreover, to make the situation slightly more

complex, you now need to have both a range lookup for the weight and a standard lookup for

the courier name and the country. As we did with the banding, we are going to show more

than one solution to the same problem, for educational purposes.

Using DAX to Resolve Complex Relationships

Because you cannot resolve the relationship between the couriers and the orders tables using

the standard PowerPivot relationship model, you need to leverage your DAX knowledge to

build an expression that performs the computation.

Let us start with a simple algorithm, which you would use if you needed to perform the

computation by hand. Given a specific order, you can compute the freight for the BlueYonder

courier searching through the couriers table for a row for which these conditions are true:

■
The country is the same as the order.

■
The courier name is BlueYonder.

■
The order weight falls within the range specified by MinWeight and MaxWeight.

You are confident that, if such a row exists, it will be unique because of how you built

the couriers table. When you find that row, its Freight value is the freight cost you were

looking for.

To express this in DAX, you use the FILTER function that, as its name suggests, performs

a filter on a table returning another table, which is the result of filtering the original table

under the condition specified. We refer to this table as the
filtered table
. The expression to

obtain the filtered table is the following one:

FILTER( Couriers,

Couriers[Country] = Orders[Country]

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

&& Orders[Weight] < Couriers[MaxWeight]

&& Couriers[Courier] = "BlueYonder" )