Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

How to populate a reference column while allowing relationships to work

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

ContractIdClientNameStartDateEndDate
1FirstCustomer1/1/201912/31/2019
2SecondCustomer6/1/20195/31/2020
3FirstCustomer1/1/202012/31/2020

 

ServiceRequests

IdClientNameDateContractId
1FirstCustomer4/1/2019???? (should be "1")
2FirstCustomer4/1/2020???? (should be "3")
3SecondCustomer7/1/2019???? (should be "2")
4SecondCustomer7/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])
    )
Need Help

2 Replies

  • v-eachen-msft's avatar
    v-eachen-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.