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

[Datazen]

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])

)

)

)

[AntrikshSharma · Accepted solution]

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] )
        )
    )
)

[Datazen]

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] )

)

)

)

[harshnathani · Accepted solution]

HI @Datazen ,

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

[AntrikshSharma · Accepted solution]

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] )
        )
    )
)

[Datazen]

Thanks Antriksh. This makes sense now! 

John

[harshnathani]

Hi @Datazen ,

Have a look at the video for customer churn analysis

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

Regards,

HN

[Datazen]

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.

[lbendlin]

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.