using inactive relationship

[Zyg_D]

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?

[Icey · Accepted solution]

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 · Accepted solution]

This works without changing any relationship: 

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

[Icey · Accepted solution]

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]

---

@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/11973... 

[amitchandak]

@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

[nvprasad]

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]

---

@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: