Forum Discussion

Zyg_D's avatar
Zyg_D
Continued Contributor
6 years ago
Solved

using inactive relationship

This is my data:

And the relationships (one of them is inactive):

When I try to create a ToyColor calculated column in the Toy table I get wrong results:

Is there a way to use inactive relationship so that the formula gives me Green instead of White?

Need Help
Show and Tell
  • Icey's avatar
    Icey
    6 years ago

    Hi Zyg_D ,

     

    In your data, the ColorID of Cat is 2 and that of Plant is 3. But in table PetToy, when Pet = Cat, Toy = Plant. In the existing relationships, the ColorID will get value like this:

    'Toy'[Toy] -> 'PetToy'[Toy] -> 'PetToy'[Pet] -> 'Pet'[Pet] -> 'Pet'[ColorID] -> 'Color'[ColorID].

    So, it will return White for Plant.

     

    It is suggested to make the relationship between 'Pet' and 'Color' inactive, too.

     

     

    Best Regards,

    Icey

     

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

  • Zyg_D's avatar
    Zyg_D
    5 years ago

    This works without changing any relationship: 

    ToyColor = 
CALCULATE (
    MINX (
        Toy,
        RELATED ( Color[Color] )
    ),
    USERELATIONSHIP ( Toy[ColorID], Color[ColorID] ),
    ALLEXCEPT ( Toy, Toy[Toy] )
)

     

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Zyg_D , Most of these joins are bi-direction make them single direction join from 1 to Many. Unless most needed do not use bi-directional join.

    Second, these 1-1 join, try to make them 1-Many join from Dimension/master/lookup to Fact/Transaction data.

     

    If they are truly 1-1 then only make them 1-1

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi Zyg_D ,

     

    In your data, the ColorID of Cat is 2 and that of Plant is 3. But in table PetToy, when Pet = Cat, Toy = Plant. In the existing relationships, the ColorID will get value like this:

    'Toy'[Toy] -> 'PetToy'[Toy] -> 'PetToy'[Pet] -> 'Pet'[Pet] -> 'Pet'[ColorID] -> 'Color'[ColorID].

    So, it will return White for Plant.

     

    It is suggested to make the relationship between 'Pet' and 'Color' inactive, too.

     

     

    Best Regards,

    Icey

     

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

    • Zyg_D's avatar
      Zyg_D
      Continued Contributor
      6 years ago
      Icey wrote:

      Hi Zyg_D ,

       

      In your data, the ColorID of Cat is 2 and that of Plant is 3. But in table PetToy, when Pet = Cat, Toy = Plant. In the existing relationships, the ColorID will get value like this:

      'Toy'[Toy] -> 'PetToy'[Toy] -> 'PetToy'[Pet] -> 'Pet'[Pet] -> 'Pet'[ColorID] -> 'Color'[ColorID].

      So, it will return White for Plant.

       

      It is suggested to make the relationship between 'Pet' and 'Color' inactive, too.

       

       

      Best Regards,

      Icey

      Hello, Icey. Thank you for digging into my case. I really have not thought about the option to leave both relations inactive. This is the followup question: https://community.powerbi.com/t5/Desktop/Calculated-columns-USERELATIONSHIP-or-LOOKUPVALUE/m-p/1197385#M536029 

  • nvprasad's avatar
    nvprasad
    Solution Sage
    6 years ago

    Hi,

    Please use the "Userelationship" function to turn inactive relationships to active.

    Appreciate a Kudos! 🙂
    If this helps and resolves the issue, please mark it as a Solution! 🙂

    Regards,
    N V Durga Prasad

     

    • Zyg_D's avatar
      Zyg_D
      Continued Contributor
      6 years ago
      nvprasad wrote:

      Hi,

      Please use the "Userelationship" function to turn inactive relationships to active.

      Appreciate a Kudos! 🙂
      If this helps and resolves the issue, please mark it as a Solution! 🙂

      Regards,
      N V Durga Prasad

      Thanks, but USERELATIONSHIP does not work:

  • Zyg_D's avatar
    Zyg_D
    Continued Contributor
    5 years ago

    This works without changing any relationship: 

    ToyColor = 
CALCULATE (
    MINX (
        Toy,
        RELATED ( Color[Color] )
    ),
    USERELATIONSHIP ( Toy[ColorID], Color[ColorID] ),
    ALLEXCEPT ( Toy, Toy[Toy] )
)