lookupvalue sometimes works

[Anonymous]

Hello all,

i have 2 face tables that are not related to one another but are Via customer Dim table:

I need to create a calc colum in Fact Table 2 that looks up a column from Fact Table 1

based on some columns that match in BOTH tables:

very generic.

the problem is that somethimes it works and somthimes it doent (when it totally should!!)

and somthines it dosnt work:

I know when I get a blank thats because not ALL the condiotns are met but they are. they all meet but I still get a blank

any ideas? 

[v-angzheng-msft]

Hi, @Anonymous 

As @HotChilli  mentioned above, hidden spaces and other invisible characters often affect the lookup result. An easy way is to use the Len function to check whether the string lengths are the same.
Remove the hidden characters and often you will get the correct result.

Best Regards,
Community Support Team _ Zeon Zheng

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

[HotChilli]

I think you will have to investigate the data quality of the columns as it looks like there's a difference between tables. You can do this in power Query, using column quality, distribution, profile from the View menu.

Are the ID columns text? If so they might need a Trim to remove spaces etc.