A table of multiple values was supplied where a single value was expected.

[H_insight]

Hi,

 I have a small model of two tables, connected by the "Date" Column. Sample Table1

When I add a "Date" filter to the page (i.e. to show last week), the rows with no "Total Sales" data disappear (which is normal). But I am trying to see what each individual has done, even if it was nothing/null/0/blank.

Thought of removing the filter on the calendar table, but still, no data is showing and got the error of "A table of multiple values was supplied where a single value was expected."

Total Sales 2 = CALCULATE([Total Sales], FILTER('Calendar', ALL('Calendar'[Date])))

Expected outcome:

any idea?

Sample File.

Thanks

[v-cazheng-msft · Accepted solution]

Hi @H_insight ,

According to the syntax of Filter function, the second parameter should be a Boolean expression that is to be evaluated for each row of the table but not a table. For All function, it will return all the rows of the table and it couldn’t be used as the second parameter of Filter.

FILTER function (DAX) - DAX | Microsoft Docs

ALL function (DAX) - DAX | Microsoft Docs

You may try this Measure.

Total Sales 2 = CALCULATE([Total Sales],ALL('Calendar'[Date]))

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please provide a new download lind of your sample pbix file for we couldn’t download it with the existing link. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[Greg_Deckler]

@H_insight Try:

Total Sales 2 = 
VAR __Total = CALCULATE([Total Sales], FILTER('Calendar', ALL('Calendar'[Date])))
RETURN
IF(__Total <> BLANK(), __Total, 0)

Or, more succinctly:

Total Sales 2 = CALCULATE([Total Sales], FILTER('Calendar', ALL('Calendar'[Date]))) + 0

[H_insight]

@Greg_Deckler  You are super Fast!

however, I am getting the same error: