Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Slicer multiple selection error

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 )

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    8 years ago

    Hi Anonymous,

     

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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 )

     

    • v-frfei-msft's avatar
      v-frfei-msft
      Community Support
      8 years ago

      Hi Anonymous,

       

      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

    • erikajain02's avatar
      erikajain02
      Resolver I
      8 years ago

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