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.
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.
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]))