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

     

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