I have 3 fact tables and incomplete connecting IDs like a product key

[Anonymous]

I'm currently developing a model that has three fact tables for which I've taken certain attributes and created look tables based each fact table. The issues arises when I'm trying to create measures that considers all these relationships. I've gotten most of the relationships to work the way they're supposed to. However, I have data columns that only show up on one fact table and I need this information on the missing tables to create a uniform look up table for all three fact tables. 

I've tried using the lookup value function to pull in the missing information (Like a VLOOKUP). However, a message is returned that says the following below and I'm unsure on how to fix this? I need to pull in the product category ID and product title from the FMSI fact table using the order number or order ID and have it pulled into sales and RMA fact table to filter for product category as well as replace missing title fields in RMA fact table that has most of titles from the product title attribute but for some reason, I don't understand some titles are missing which I can pull from the FMSI fact table. Additonally, when working with three fact tables I understand from best practices its best to use one look table for all fact tables. I hope a POWER BI SUPER HERO IS OUT THERE WHO CAN HELP ME.......

[Ashish_Mathur]

Hi,

That error means that there are duplicate CustomerID's available in the FMSI_Data table.  Give more conditions in the LOKUPVALUE() function to narrow down the row returned in the FMSI_Data table to just 1.

[Anonymous]

Thank you, Ashish! I appreciate your feedback!

[V-pazhen-msft · Accepted solution]

@Anonymous 

Use the alternative in the post. 

Solved: LOOKUPVALUE - "A table of multiple values was supp... - Microsoft Power BI Community

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you, so much! This was the answer to my issue.

[amitchandak]

@Anonymous , You are getting more than one value, Use max on the first column if needed.

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[Anonymous]

Hi Amit,

Thank you for your reply! 

I appreciate you taking out the time to answer my question. However, I've tried including the MAX function within the Lookupvalue function and it does not accept it as a parameter within a calculated column. Unless my measure is wrong. Can you please clarify or provide additional options? Aside, from this is there a way for the repor consumer to toggle the row values dynamically if drilling into a report? Or is this only available on dashboards.