Forum Discussion
LOOKUPVALUE returns blank and unable to recognize dates
Thank you Dale. I have solved the issue.
In the schema, even though the LOOKUPVALUE formula only warrants that both the BALANCES and FXRATES tables are populated, apparently, I will also need to populate a third table (BENCHMARK table) which has a relationship with the Master Date Table. My conclusion is that there may be a completeness requirement for the formulae to work. Do you have an idea why that is? Please see my schema below.
Microsoft EmployeeHi blazeken,
1. Are there any error messages?
2. Is there a relationship between Balances and Master?
3. Did the formula work once?
Could you please provide a dummy sample? It's hard to find out the cause without data.
My test:
Best Regards!
Dale
Thank you Dale. I have solved the issue.
In the schema, even though the LOOKUPVALUE formula only warrants that both the BALANCES and FXRATES tables are populated, apparently, I will also need to populate a third table (BENCHMARK table) which has a relationship with the Master Date Table. My conclusion is that there may be a completeness requirement for the formulae to work. Do you have an idea why that is? Please see my schema below.
- v-jiascu-msft
Hi blazeken,
Could you please share the formula? What I can guess only is the unique value can't be determined due to there are many 1:* relationships. If you can post some snapshots about the error, that would be great.
Best Regards!
Dale
The LOOKUPVALUE formula is what I shared in my first post.
The reason the LOOKUPVALUE formula was not populating in BALANCSE was because the cross-filter between DateMaster and Benchmark (as shown in the schema) was set as "BOTH", hence requiring that the August values be present in the Benchmark table as well.
- v-jiascu-msft
Hi blazeken,
The formula in your first post only have two search column parameters. So I don't know how you will add the third table. The formula should work. I have no other idea without data.
Best Regards!
Dale