Slicer multiple selection error

[mruhullah]

I want to compare day by day revenue based on country and area. I am using below query and getting data only if i select one country or area. when i select multiple country or area i am getting error (a table of multiple values was supplied where a single value was expected). Can ayone help me how to get data when we select multiple selection from slicer.

Revenue D/D =
VAR TodayY = MAXX ( ALL ( Revenue ), Revenue[Create Date] )
VAR SelectCountry = VALUES ( 'Sales Area'[country] )
VAR SelectArea = VALUES ( 'Sales Area'[Sales Area] )
VAR Yesterday = TodayY - 1
VAR Revyesterday =
CALCULATE (
SUM(Revenue[Total_Revenue]),
FILTER (Revenue, Revenue[Create Date] = Yesterday && Revenue[Country]=SelectCountry && Revenue[Sales Area]= SelectArea)
)
VAR Revtoday =
CALCULATE (
SUM(Revenue[Total_Revenue]),
FILTER ( Revenue , Revenue[Create Date] = TodayY && Revenue[Country]=SelectCountry && Revenue[Sales Area]= SelectArea)
)
RETURN
( Revtoday - Revyesterday )

[mruhullah]

I want to compare day by day revenue based on country and area. I am using below query and getting data only if i select one country or area. when i select multiple country or area i am getting error (a table of multiple values was supplied where a single value was expected). Can ayone help me how to get data when we select multiple selection from slicer.

Revenue D/D =
VAR TodayY = MAXX ( ALL ( Revenue ), Revenue[Create Date] )
VAR SelectCountry = VALUES ( 'Sales Area'[country] )
VAR SelectArea = VALUES ( 'Sales Area'[Sales Area] )
VAR Yesterday = TodayY - 1
VAR Revyesterday =
CALCULATE (
SUM(Revenue[Total_Revenue]),
FILTER (Revenue, Revenue[Create Date] = Yesterday && Revenue[Country]=SelectCountry && Revenue[Sales Area]= SelectArea)
)
VAR Revtoday =
CALCULATE (
SUM(Revenue[Total_Revenue]),
FILTER ( Revenue , Revenue[Create Date] = TodayY && Revenue[Country]=SelectCountry && Revenue[Sales Area]= SelectArea)
)
RETURN
( Revtoday - Revyesterday )

[erikajain02]

You need to retun value with some aggregation function...eg : SUMX(( Revtoday - Revyesterday ))

[v-frfei-msft · Accepted solution]

Hi @mruhullah,

Based on my test, we can update your formula as below to meet your requirement.

Revenue D/D = 
VAR TodayY = CALCULATE(MAX(Revenue[Create Date]),ALLEXCEPT(Revenue,Revenue[Create Date]))
VAR Yesterday = TodayY - 1
VAR Revyesterday =
CALCULATE (
SUM(Revenue[Total_Revenue]),
ALLSELECTED(Revenue),FILTER (Revenue, Revenue[Create Date] = Yesterday )
)
VAR Revtoday =
CALCULATE (
SUM(Revenue[Total_Revenue]),
ALLSELECTED(Revenue),FILTER ( Revenue , Revenue[Create Date] = TodayY)
)
RETURN
Revtoday – Revyesterday

For more details, please check the pbix as attached.

https://www.dropbox.com/s/e9ydh1nwpbsia9y/Slicer%20multiple%20selection%20error.pbix?dl=0

Regards,

Frank

[mruhullah]

Thanks @v-frfei-msft