PowerPivot Denali: Working with multiple relationships between two tables

In the SQL server Denali release it will be possible to work with multiple relationships between two tables. This blog post will show you how to use this in DAX to create a measure that uses the inactive relationship.

Consider the following model based on adventureworks (using our new Diagram view):

As you can see the FactInternetsales table has three different dates’s that are related to one sale transaction: an Order date, a Due date and a Ship date. You also notice that we have three lines between the FactInternetsales table and the Dimdate table, and one of them looks different.

The relationship with the solid line represents the active relationship. An active relationship means that by default Excel and DAX will use this relationship to when traveling the relationship.

If you were to drag in a column of the DimDate table on rows and a measure from the FactInternsales table into the values area of the Pivottable it would automaticly use the OrderDateKey relationship. Only one relationship can be active at any time between two tables.

But we can use the other (inactive) relationships using DAX to get the sum of salesamount on the shipped date.  All we need to do is to tell the sum of salesamount function to use the other relationship.  The DAX would look like this:

 

=CALCULATE([Sum of SalesAmount],
           USERELATIONSHIP(DimDate[DateKey], FactInternetSales[ShipDateKey]))

 

This will calculate the sum of salesamount using the shipdatekey relationship defined between DimDate[DateKey] and  FactInternetSales[ShipDateKey] :