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] :
This is a great solution for a lot of practical cases. Very useful.
This is great stuff. Thanks for sharing Kasper!
This is very helpful. Thanks for posting.
Hi Kaspar, I have a question about adding a column to my data table which I cannot solve it with RELATED nor with USERELATIONSHIP. I have two tables:
– the data table with 4 fields: UnitCode1, UnitCode2, Projectrelated (yes/No) and Value.
– a table with General unit code and the Unit name
I want a column with the Unit name, which is dependant on whether an entry is Project related or not. I linked the column UnitCode1 in the data table to the General unit code.
However, If an entry in the data table is Projectrelated, UnitCode2 should be used to determine the unit.
If I use RELATED, Powerpivot will allways use UnitCode1. I cannot use USERELATIONSHIP either because it requires CALCULATE or a similar function. Any suggestions? Many thanks! Jeroen.
Sorry, I meant KaspEr