can't create direct active relationship

[Anonymous]

 i am trying to create active relationship between two tables . i am getting error you can't create direct active relationship between  Out_Duration_ChildTMC_Affiliate_Final and  Out_RefernceDates because  that would  introduct ambiguity between tables D_scenario  and Out_Duration_ChildTMC_Affiliate_Final . To make this relationshiop active,Deactivate or delete one of the realtionship between _scenario  and Out_Duration_ChildTMC_Affiliate_Final  first.

i am trying to  take fixed shipments  value from  Out_Duration_ChildTMC_Affiliate_Fina based on  Affiliate  and ChildTMC.                  

Out_RefernceDates

Affiliate                  ChildTMC            ID                 LPPYear 

 1002020               LOOF                  898372           2019

 Out_Duration_ChildTMC_Affiliate_Final

 Affiliate                  ChildTMC            ID                 LPPYear   FixedShipments

 1002020               LOOF                  898372           2019             0

final output refernce :

Out_RefernceDates

Affiliate                  ChildTMC            ID                 LPPYear   FixedShipments

 1002020               LOOF                  898372           2019            0

i have tired with USERELATIONSHIP   function but its not working 

XYZ = CALCULATE(SUM(Out_Duration_ChildTMC_Affiliate_Final[Fixed Shipments(Tons)]),USERELATIONSHIP(Out_Duration_ChildTMC_Affiliate_Final[Affiliate],Out_ReferenceDates[Affiliate]))

thanks in advance. i am looking   for help . 

[ValtteriN]

Hi,

Your idea with the userelationship is a good if you are not able to create active relationship. Could you provide more details as to how your measure is not currently working? Additionally it would be good to have some sort of idea about how your data model is structured. Anyway, if you are trying to calculate the fixed shipments based on Affiliate and ChildTMC you could consider making a combined primary key out of these two by using CONCAT either in powerqeury, in your database or within a calculated column. Afterwards you can use this key to create the relationship. 

[Anonymous]

@ValtteriN  my measure return wrong value . In both table common value  ChildTMC ,Affiliate.
for example 

Affiliate                  ChildTMC        

1002020                LOOF                   

FixedShipments  value  zero  but i have applied measure its return value 17.50 

[Anonymous · Accepted solution]

Hi @Anonymous ,

USERRELATIONSHIP function works.

My sample data is as follows.

What you need to pay attention to is that your relationship is to be created an inactive relationship, uncheck this option.

Measure = CALCULATE(SUM('Out_Duration_ChildTMC_Affiliate_Final'[FixedShipments]),USERELATIONSHIP(Out_Duration_ChildTMC_Affiliate_Final[Affiliate],Out_RefernceDates[Affiliate]))

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.