How to populate a reference column while allowing relationships to work

[Anonymous]

Hi,

I have two tables, Contracts and ServiceRequests.  ServiceRequests does not have the ContractId in it, but I want to have ContractId on ServiceRequests, so I can make a relationship and build a report off the link.

There is enough data in both tables to look this up from ServiceRequests.  However, I cannot figure it out.  I managed to add ContractId to ServiceRequests via a custom column in DAX, but then it gave me an error about circular dependencies when i tried to create the relationship.

The relationship to lookup ContractId from ServiceRequests is:

Contracts.ClientName = ServiceRequests.ClientName

&& ServiceRequests.Date >= Contracts.StartDate

&& ServiceRequests.Date <= Contracts.EndDate

There is only ever 1 possible ContractId for each ServiceRequest record.  Sometimes a ServiceRequest won't have an associated Contract, and this column value could be blank.

Contracts

ContractId	ClientName	StartDate	EndDate
1	FirstCustomer	1/1/2019	12/31/2019
2	SecondCustomer	6/1/2019	5/31/2020
3	FirstCustomer	1/1/2020	12/31/2020

ServiceRequests

Id	ClientName	Date	ContractId
1	FirstCustomer	4/1/2019	???? (should be "1")
2	FirstCustomer	4/1/2020	???? (should be "3")
3	SecondCustomer	7/1/2019	???? (should be "2")
4	SecondCustomer	7/1/2020	???? (should be blank)

Can anyone advise how to do this properly?  It seems like it should be in M, because then I could create a relationship between the two ContractId fields.  I figured out how to look this up in DAX, but then got circular dependencies...

Any help or advice would be much appreciated.  And in case you are wondering, I have no ability to pre-populate ContractId before it is brought into PowerBI.

Thanks very much!

PS this is how I looked up ContractId on ServiceRequests table from DAX... again, it worked, but won't allow me to use the column for a relationship...

ContractId =

calculate(

    FIRSTNONBLANK(Contracts[ContractId], 1),

    REMOVEFILTERS(),

    FILTER(All(Contracts), Contracts[ClientName] = ServiceRequests [ClientName]

               && Contracts[Start Date] <= ServiceRequests [Date]

               && Contracts[End Date] >= ServiceRequests [Date])

    )

[v-eachen-msft]

Hi @Anonymous ,

Your DAX is right, all you need to do is creating a copy table 'Table1' of Contracts.

Then you could create relationship between Table1 and ServiceRequests.

[Anonymous]

That is a potential workaround, though creating duplicate tables just for lookup purposes seems rather inefficient and over-complicated.  I had been hoping to do this via M instead of DAX, to avoid this circular issue.

Because of other lookups between these two tables, I actually still get circular reference issues (and would therefore have to duplicate both tables).  Going to see if anyone else has any other ideas in this respect.