topic Re: A table of multiple values was supplied where a single value was expected. in DAX Commands and Tips

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

H_insight — Thu, 07 Jul 2022 19:52:53 GMT

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

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

Greg_Deckler — Thu, 07 Jul 2022 19:56:08 GMT

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

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

H_insight — Thu, 07 Jul 2022 19:58:45 GMT

Greg_Deckler You are super Fast!

however, I am getting the same error:

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

v-cazheng-msft — Tue, 12 Jul 2022 08:25:25 GMT

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