Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Zyg_D
Continued Contributor
Continued Contributor

LOOKUPVALUE, TREATAS not to respond to the active relationships

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:

data_model.JPG

This is the data:

data.JPG

LOOKUPVALUE fails:

lookupvalue.JPG

TREATAS fails:

treatas.JPG

The goal:

Goal.JPG

1 ACCEPTED SOLUTION
Zyg_D
Continued Contributor
Continued Contributor

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

View solution in original post

2 REPLIES 2
Zyg_D
Continued Contributor
Continued Contributor

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
Community Support
Community Support

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.

 

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.