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 in...
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] )
)

     

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