LOOKUPVALUE, TREATAS not to respond to the active relationships

[Zyg_D]

My goal is to lookup the correct values in the calculated column ToyColor, without changing the relationships. My understanding is that LOOKUPVALUE and TREATAS do not work, because of the active relationships, which I do not want to remove or change. 

This is the data model:

This is the data:

LOOKUPVALUE fails:

TREATAS fails:

The goal:

[Zyg_D · Accepted solution]

When both-sided cross filter directions are used, it seems, LOOKUPVALUE and TREATAS work properly in two cases:

• Only inactive relationships go to the table from which the looked up values are to be retrieved
• There are no relationships to the table from which the looked up values are to be retrieved

With an active direct relationship it will probably work too, but then there is also a RELATED function. 

With only one, but indirect relationship between the tables, it is impossible to make LOOKUPVALUE and TREATAS work properly. It covers two cases - impossible to make these functions to work credibly when:

• Indirect active relationship + no direct relationship
• indirect active relationship + direct inactive relationship

[v-xuding-msft]

Hi @Zyg_D ,

The result can't get "Green" (ColorID =3 in table "Color"). Because there is not a direct relationship between table "Toy" and "Color". And there is not a value whose color ID is 3 in "PetToy" and "Pet".  Then the "Color" table just returns "Black"(ColorID = 1).

If don't change the relationship, I don't think we can implement what you want.