No duplicates present, but LOOKUPVALUE still says "A table of multiple values was supplied... "

[MikeO]

I am using the following formula to determine end date of an advertisement:

End Date =
IF (
'Source Marketing Merged'[Run Number] = 1,
TODAY (),
LOOKUPVALUE('Source Marketing Merged'[Adjusted Run Date],'Source Marketing Merged'[Newspaper],'Source Marketing Merged'[Newspaper],'Source Marketing Merged'[Store Name],'Source Marketing Merged'[Store Name],'Source Marketing Merged'[Run Number],'Source Marketing Merged'[Run Number]-1)

The last advertisement to run always has a Run Number of 1, thus it gets an End Date equal to today.  Every additional advertisement gets an End Date equal to the Adjusted Run Date of the next advertisement to run in the newspaper for that store.

The error result I am getting is "A table of multiple values was supplied where a single value was expected."  This should mean there are duplicates present, but I tested for duplicates by combining Source Marketing Merged'[Newspaper]&,'Source Marketing Merged'[Store Name]&'Source Marketing Merged'[Run Number] all into one column, then using the following formula to check for duplicates:

Duplicate Check =
CALCULATE ( COUNT ( 'Source Marketing Merged'[Source, Store and Run Date] ), ALLEXCEPT ( 'Source Marketing Merged', 'Source Marketing Merged'[Source, Store and Run Date] ) )

The result shows no duplicates, and yet I still get the same error message above on my LookupValue formula.  Just to make sure my Duplicate Check formula wasn't the problem, I also used this:

Duplicate Run Dates = CALCULATE(COUNTA('Source Marketing Merged'[Source, Store and Run Date]),filter('Source Marketing Merged','Source Marketing Merged'[Source, Store and Run Date]=EARLIER('Source Marketing Merged'[Source, Store and Run Date])))

Both formulas are showing no duplicates.  Does anyone know why this formula, which worked before today, is having this problem when there are no duplicates?

Thank you!

[MikeO · Accepted solution]

I didn't get an answer to the error that I was getting, but I did find a solution.  Specifically, I had a separate table that had everything except for store names.  The lookupvalue formula works on that table for calculating End Date, so I simply reference that table and pull end date.  So while I did not get an answer to the specific problem I was having (i.e., the lookupvalue formula thinking I had duplicates when I did not), I did find another way to get to my answer.

[AlB]

Hi @MikeO

Can you provide the pbix file?