Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
D_PBI
Post Partisan
Post Partisan

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

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]

D_PBI_0-1746613832668.png


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.

D_PBI_0-1746614241279.png

 


Thanks.

2 ACCEPTED SOLUTIONS
MattiaFratello
Solution Supplier
Solution Supplier

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

View solution in original post

ribisht17
Community Champion
Community Champion

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

View solution in original post

6 REPLIES 6
v-pbandela-msft
Community Support
Community Support

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
Community Champion
Community Champion

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
Solution Supplier
Solution Supplier

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

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
Solution Supplier
Solution Supplier

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

@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)?

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.