Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

can't create direct active relationship

 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 . 

Help appreciated
Help me
Help Requested
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

     

3 Replies

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    4 years ago

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

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

        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.