How to manage multiple relationship?

Question

Hello everyone,&nbsp;I have the following strucutre:DateDim table - Date DATEPersonActivity Table - ActivityStartDate DATE, ActivityEndDate DATE&nbsp;I want to filter the report based on the ActivityStartDate and ActivityEndDate, I'm trying to set relation between DataDim and PersonActivity table but only one relation is active at a time.&nbsp;How can I manage to have filer on multiple filed from One table linked with multiple fields to dimension table.&nbsp;Thanks,&nbsp;

bhaveshpatel · Answer

Hi&nbsp;iLikeAzureSQL&nbsp;A&nbsp;BLOG from Matt Allington&nbsp;can solve the problem from you. In addition to this, the link from SQLBI also has some more detailed explaination on this topic.

dkay84_powerbi · Answer

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/

ilikeazuresql · Answer

Thank you for reference,&nbsp;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 ?

v-ljerr-msft · Answer

Hi&nbsp;iLikeAzureSQL,&nbsp;You don't need to enable Both direction for the relationship in this scenario.&nbsp;&nbsp;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.&nbsp;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.&nbsp;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.&nbsp;Regards

Forum Discussion

How to manage multiple relationship?

4 Replies

Related content

Creating Multiple manage relationships at ones

Managing Relationships

Relationship

Ambiguity Error Managing Relationships in data model

resize manage relationships window