LOOKUPVALUE returns blank and unable to recognize dates

[blazeken]

I have two tables

a. BALANCES: contains balances of certain accounts, denominated in the local currency

b. FXRATES: contains conversion rates to convert from USD to whatever the local currency is. 

BALANCES contains the following columns:

a. ReckoningDate - in date format

b. AccountCode - in text format

c. LC Balance - a number

d. USDBalance - a calculated column with the following formula

USDBalance = Balances[LCBalance]*LOOKUPVALUE(FXRates[PerUSD],FXRates[ReckonDate], Balances[ReckonDate],FXRates[Currency],RELATED(Master[CCY]))   ]

FXRATES contains the following columns:

a. ReckoningDate - in date format

b. Currency - a three character text (e.g., GBP, EUR, AUD, etc.)

c. FXRate - in number format which has the actual exchange rate quote

d. PerUSD- a standardized quote in number format which can be used as a multiplier

For some reason, the calculated column Balances[USDBalance] shows a blank for newly entered and refreshed data for the month of August, whereas it is populated accordingly for the month of July and any date earlier than that. I have narrowed down the problem to the fact that the LOOKUPVALUE formula could not find the August data which I loaded into both BALANCES and FXRATES tables. 

Here is a background of my process:

1. I download excel reports from various source websites

2. I run a script to convert them into tabular format

3. I append each month's data to an Access Database

4. I refresh the Power BI Data model to capture these recently loaded data. 

Here is what I have done so far in an attempt to fix this issue.

1. I have reinstalled Power BI 64-bit version

2. I have deleted the August data from the access database and re-imported the data from excel. 

3. I have run separate visualizations on the BALANCES and FXRATES tables independently and I can see that Power BI is able to graph the data properly with the August data reflected on the x-axis. 

4. I have checked and verified that the data types are the same - in date format on Power BI. 

5. In query editor, I have modified the query to even convert the ReckoningDate fields in both the BALANCES and FXRATES queries to date format

6. I have expanded the format of the date to show even the time stamp and I can see that they are all as of a certain date at 12:00:00 AM. 

7. I do not see any duplicates in the data, and I have monitored the tables accordingly to see that indeed the appropriate data exists on the table for the LOOKUPVALUE to work.

8. In LOOKUPVALUE, I replaced the search values to constants and can check that the formula does not work because it could not find the dates.  I can see that the dates are actually present in both tables and shown in the same format. 

Does anyone have an idea of another potential solution?  At this point, I am only looking for potential ideas to try.  I am unable to share or paste the actual data here given its sensitive nature.  Let me know if you need additional information to address the issue. 

[v-jiascu-msft]

Hi @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

[blazeken · Accepted solution]

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

[blazeken]

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

[blazeken]

Thank you for your help Dale.  This issue is now solved.