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
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:
Couriers[Country] = Orders[Country]
&& Couriers[MinWeight] <= Orders[Weight]
&& Orders[Weight] < Couriers[MaxWeight]
&& Couriers[Courier] = "BlueYonder" )