Forum Discussion
How to manage multiple relationship?
I'm likely wrong but I think that blog post is a bit more than was being asked for (but very informative).
Try using the function USERELATIONSHIP as described here: https://www.sqlbi.com/articles/userelationship-in-calculated-columns/
Thank you for reference,
I read that but in my model I can't enable BOTH directional, I'm using DirectQuery and that might be limiting my options!
Any other suggestions ?
Hi iLikeAzureSQL,
You don't need to enable Both direction for the relationship in this scenario.
You can create multiple relationships between these two tables, and only one can be active at a time. The remaining one can be used by DAX formulas calling USERELATIONSHIP in CALCULATE or CALCULATETABLE. This affects every measure that has to filter the time by using a date column other than the one used by the active relationship.
For example, consider the data model shown like below. There are three different relationships between Sales and Date, and only the one between Sales[OrderDateKey] and Date[DateKey] is active.
You can create two measures for sales amount, based on different usage of the selection on the Date table.
[Ordered Amount] := SUMX ( Sales, Sales[Unit Price] * Sales[Quantity] ) [Delivered Amount] := CALCULATE ( SUMX ( Sales, Sales[Unit Price] * Sales[Quantity] ), USERELATIONSHIP ( Sales[DeliveryDateKey], 'Date'[DateKey] ) )
The first measure, Ordered Amount, uses the active relationship between Sales and Date, based on Sales[OrderDateKey]. The second measure, Delivered Amount, executes the same DAX expression using the relationship based on Sales[DeliveryDateKey]. USERELATIONSHIP changes the active relationship between Sales and Date in the filter context defined by CALCULATE.
Regards
