topic Table of Multiple Values Supplied Error in DAX Commands and Tips

Table of Multiple Values Supplied Error

samdep — Fri, 04 Mar 2022 16:30:45 GMT

Hi Community - I am getting an error with the below DAX - specifically "A table of multiple values was supplied where a single value was expected" and I've been reading about the meaning of this error, but am unsure how to resolve in my specific case. Is there anything glaring that I am overlooking? Any thoughts would be much appreciated.

The sample table below shows my data - and 'New Column' is my goal.

Previous Amount =

IF(

OR(Opportunity[StageName] = "Closed Lost", Opportunity[Recurring__c] = FALSE()), BLANK(),

LOOKUPVALUE(Opportunity[Amount], Opportunity[CloseDate],

CALCULATE(MAX(Opportunity[CloseDate]),

FILTER(Opportunity, Opportunity[AccountId] = EARLIER(Opportunity[AccountId])

&& Opportunity[CloseDate] = EARLIER(Opportunity[CloseDate])

&& Opportunity[StageName] = "Closed Won"

&& Opportunity[Recurring__c] = TRUE())),

Opportunity[AccountId], Opportunity[AccountId], Opportunity[Recurring__c], TRUE())

)

Many Thanks!

AccountId	Amount	Stage	Recurring	CloseDate	New Column
A	$10	Closed Won	True	2/1/22	$5 - Previous Value (1/1/22)
B	$5	Closed Won	False	2/11/22	blank -as not recurring and no prior value
A	$5	Closed Won	True	1/1/22	$5 - Previous Value (12/1/21)
C	$50	Closed Lost	True	2/1/22	blank - while recurring, stage is closed lost.
C	$100	Closed Won	True	1/1/22	blank - no prior value
A	$5	Closed Won	True	12/1/22	blank - no prior value

Re: Table of Multiple Values Supplied Error

amitchandak — Fri, 04 Mar 2022 17:04:37 GMT

samdep , Try like

New column =
var _max = CALCULATE(MAX(Opportunity[CloseDate]),
FILTER(Opportunity, Opportunity[AccountId] = EARLIER(Opportunity[AccountId])
&& Opportunity[CloseDate] = EARLIER(Opportunity[CloseDate])
&& Opportunity[StageName] = "Closed Won"
&& Opportunity[Recurring__c] = TRUE()))

return
CALCULATE(Sum(Opportunity[Amount]),
FILTER(Opportunity, Opportunity[AccountId] = EARLIER(Opportunity[AccountId])
&& Opportunity[CloseDate] = _max
&& Opportunity[StageName] = "Closed Won"
&& Opportunity[Recurring__c] = TRUE()))

Re: Table of Multiple Values Supplied Error

samdep — Fri, 04 Mar 2022 18:20:28 GMT

Thank you so much! This works perfectly!!