Power BI - If a date is between 2 dates using relationships

user_5675992 0
Viewed 507

I am trying to determine if a date is between 2 other dates that are is 2 different tables using Power BI.

For simplicity, here is the model that I have : enter image description here

TableB is the bridge table between TableA and TableC. I have an inactive relationship between tableA and TableC.

I have tried the following logic to check if TableC.createdDate is between TableA.startDate and TableA.endDate :

  • Create a calculated column in TableC, but I was not able to access columns in TableA
  • Create a calculated column in TableB, but I was having blank results, which is not suppose to happen
powerbi-desktop powerbi relationship reporting data-modeling
1 Answers

Have you tried using USERELATIONSHIP to force your calculated column to use the inactive relationship to TableA? documentation link

So something like

CALCULATE(
  {{created date is >start and <end}}, 
  USERELATIONSHIP('TableA'[TableB_ID],'TableC'[TableB_ID]
)
user_160330210
Related