topic Re: can't create direct active relationship in DAX Commands and Tips

can't create direct active relationship

Anonymous — Mon, 13 Dec 2021 05:17:56 GMT

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 .

Re: can't create direct active relationship

ValtteriN — Mon, 13 Dec 2021 07:15:22 GMT

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.

Re: can't create direct active relationship

Anonymous — Mon, 13 Dec 2021 08:27:28 GMT

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

Re: can't create direct active relationship

Anonymous — Mon, 20 Dec 2021 08:19:25 GMT

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.