How to manage multiple relationship?

[iLikeAzureSQL]

Hello everyone, 

I have the following strucutre:

DateDim table - Date DATE

PersonActivity Table - ActivityStartDate DATE, ActivityEndDate DATE

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.

How can I manage to have filer on multiple filed from One table linked with multiple fields to dimension table.

Thanks,

[BhaveshPatel]

Hi @iLikeAzureSQL

A BLOG from Matt Allington 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]

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]

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 ?

[v-ljerr-msft]

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