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

ERROR: A table of multiple values was supplied when a single value was expected

Anonymous — Thu, 09 Jul 2020 17:01:41 GMT

I have a customer sales table of 2018 and 2019 sales only. The Sales Date has a relationship to a DATE dimension table. I am trying to write a DAX measure will give me a count of distinct Customer ShipTo numbers for those customers who made a purchase in 2019 but not 2018. I'm trying to follow DAX code patterns that are decribed in various blog entries by several well know DAX programmers. Below is my code. There is no error when creating the measure. But when I add it to a visual I get the error:

'A table of multiple values was supplied when a single value was expected'

I'm not sure where the problem is since CALCULATE is being asked to return a scalar. Any help is appreciated.

Acquired Customers =

CALCULATE(

COUNTROWS(VALUES( VwCOPA[ShipTo] ) ),

FILTER(

VALUES(VwCOPA[ShipTo]),

VwCOPA[ShipTo] <>

CALCULATETABLE ( VALUES( VwCOPA[ShipTo]),

FILTER( all('Date'[Date]), DATESBETWEEN('Date'[Date], DATE(2018,1,1),DATE(2018,12,31))

), ALL('Date'[Date])

)

Re: ERROR: A table of multiple values was supplied when a single value was expected

lbendlin — Thu, 09 Jul 2020 22:39:21 GMT

I don't think this part

VwCOPA[ShipTo] <> CALCULATETABLE

is possible. Any particular reason for not using EXCEPT() in this scenario? It seems to be uniquely suited to answer your question.

Re: ERROR: A table of multiple values was supplied when a single value was expected

Anonymous — Fri, 10 Jul 2020 05:38:02 GMT

Hi Anonymous ,

Have a look at the video for customer churn analysis

https://www.youtube.com/watch?v=h9kRwgamLcw

Regards,

Re: ERROR: A table of multiple values was supplied when a single value was expected

Anonymous — Fri, 10 Jul 2020 15:14:47 GMT

Thanks for your reply Lbendin. I was using <> CALCULATEDTABLE because I found that construct in this article (in the second code listing there for Acquired Customers):

https://javierguillen.wordpress.com/2012/08/24/determining-customer-retention-in-dax/

I have re-written my code using the EXCEPT function as per below. But, like my first code, it shows no errors when i create it but when I add it to a visual I get the same error of: 'A table of multiple values was supplied when a single value was expected'.

Acquired Customers =

CALCULATE (

COUNTROWS ( VALUES ( VwCOPA[ShipTo] ) ),

EXCEPT (

VALUES ( VwCOPA[ShipTo] ),

CALCULATETABLE (

VALUES ( VwCOPA[ShipTo] ),

FILTER (

ALL ( 'Date'[Date] ),

DATESBETWEEN ( 'Date'[Date], DATE ( 2018, 1, 1 ), DATE ( 2018, 12, 31 ) )

ALL ( 'Date'[Date] )

)

Re: ERROR: A table of multiple values was supplied when a single value was expected

AntrikshSharma — Fri, 10 Jul 2020 15:23:04 GMT

DATESBETWEEN is returning a list of dates and you are not applying any aggregation over it, you can use this.

Acquired Customers = CALCULATE ( COUNTROWS ( VALUES ( VwCOPA[ShipTo] ) ), EXCEPT ( VALUES ( VwCOPA[ShipTo] ), CALCULATETABLE ( VALUES ( VwCOPA[ShipTo] ), FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] > DATE ( 2008, 1, 1 ) && 'Date'[Date] < DATE ( 2008, 12, 31 ) ), ALL ( 'Date'[Date] ) ) ) )

Re: ERROR: A table of multiple values was supplied when a single value was expected

Anonymous — Fri, 10 Jul 2020 15:26:30 GMT

HI Anonymous ,

Acquired Customers := COUNTROWS ( FILTER ( ADDCOLUMNS ( VALUES ( VwCOPA[ShipTo] ), "PreviousPurchase", CALCULATE ( COUNTROWS ( VwCOPA ), FILTER ( ALL ( VwCOPA ), Date[Year] = 2018 ) ), "CurrentPurchase", CALCULATE ( COUNTROWS ( VwCOPA ), FILTER ( ALL ( VwCOPA ), Date[Year] = 2019 ) ) ), [PreviousPurchase] = 0 && [CurrentPurchase] <> 0 )

Regards,

Harsh Nathani

Re: ERROR: A table of multiple values was supplied when a single value was expected

Anonymous — Fri, 10 Jul 2020 15:42:22 GMT

Thanks Harshnathani. This is helpful. As I sift through all the ways to use the various functions, and all the possible code patterns, I know I need to settle on the most optimal ones. I think some of the code patterns I have found online are now rather old. I will experiment with what you have shown.

Re: ERROR: A table of multiple values was supplied when a single value was expected

Anonymous — Mon, 13 Jul 2020 15:21:54 GMT

Thanks Antriksh. This makes sense now!

John