Using two different relationships between the same two tables in the same measure - how?

[D_PBI]

Hi,
I have a measure that calculates three tables (3 x calculatetable functions) that are appended via the union function. The measure simply counts the rows from the union. My screenshot below is one of those three Calculated Tables. You'll note there are two date filters in the same calculatetable function. The report page has a date slicer that is fed from the dimDate[Date] field. In the model, there are two relationships that are relevant to this question - these are below:
Active relationship: dimDate[Date] <<>> agreement[ExecutionDate]
Inactive relationship: dimDate[Date] <<>> agreement[ReceivedDate]

How do I complete this Calculatetable to ensure the filters are applied and the two relationships are correctly working?
Below is how the variables are assigned. The measure will appear in a matrix visual and the expanded date tiers will be Year, Quarter, and Month. The Min and Max functions will calculate on-the-fly from the user interaction and assign the variable the tier's start and end dates - as per normal PBI functionality.

Thanks.

[MattiaFratello · Accepted solution]

Hi @D_PBI, you can use USERELATIONSHIP -> https://learn.microsoft.com/en-us/dax/userelationship-function-dax

CALCULATETABLE(
    agreement,
    dimDate[Date] >= [Start Date] && dimDate[Date] <= [End Date],  -- Filters ExecutionDate
    USERELATIONSHIP(dimDate[Date], agreement[ReceivedDate])        -- Filters ReceivedDate
)

If I answered your question, please mark it as Solution

[v-pbandela-msft]

Hi @D_PBI,

Thank you for reaching out in Microsoft Community Forum.

Thank you @ribisht17 , @MattiaFratello   for the helpful response.

As suggested by ribishi17,MattiaFratello.,  I hope this information was helpful. Please let me know if you have any further questions or you'd like to discuss this further. If this answers your question, please "Accept as Solution" and give it a 'Kudos' so others can find it easily.

Please continue using Microsoft community forum.

Regards,
Pavan.

[ribisht17 · Accepted solution]

Hello @D_PBI 

Check out this guide on managing multiple date relationships in Power BI.

FilteredTable =
CALCULATETABLE(
'Agreement',
USERELATIONSHIP(dimDate[Date], Agreement[ReceivedDate]),
Agreement[ExecutionDate] >= [MinDate],
Agreement[ExecutionDate] <= [MaxDate]
)

Regards,

Ritesh

Community Champion

Please mark the answer if helpful so that it can help others

Dance-Sing with Data Projects - YouTube

[MattiaFratello]

Hi @D_PBI, is it possible to receive a sample report?

[D_PBI]

I cannot share the report in question as there is sensitive data within it.
I was hoping my question can be answered without the need for a sample report as the question, in my eyes, is generic. I would hope my opening post explains all needed. Thanks.

[MattiaFratello · Accepted solution]

Hi @D_PBI, you can use USERELATIONSHIP -> https://learn.microsoft.com/en-us/dax/userelationship-function-dax

CALCULATETABLE(
    agreement,
    dimDate[Date] >= [Start Date] && dimDate[Date] <= [End Date],  -- Filters ExecutionDate
    USERELATIONSHIP(dimDate[Date], agreement[ReceivedDate])        -- Filters ReceivedDate
)

If I answered your question, please mark it as Solution

[D_PBI]

@MattiaFratello thanks for your reply.
Specifying the USERELATIONSHIP function to tell PBI to use the dimDate[Date] <<>> agreement[Received Date] relationship is fine, but how do I instruct the code to use the filtering logic of agreement[Received Date] >= _MinDateLY && agreement[Received Date] <= _MaxDateLY without if affecting the active relationship of dimDate[Date] <<>> agreement[Execution Date] (and the filtering logic for that relationship)?