Issue in applying SWITCH case with IF condition

[Anonymous]

Hi Team,

Kindly help me applying this DAX query - SWITCH Case

Measure1 =

SWITCH(

FILTER('Raw Data', 'Raw Data'[Market Tier - Budget location]= "T40 (Incl US)"),

var aa= CALCULATE(SUM('Raw Data'[Budget]),FILTER('Raw Data','Raw Data'[Year]=2020))/1000000

return aa+ CALCULATE(SUM(Categories_US_Final[TOTAL]),FILTER(Categories_US_Final,Categories_US_Final[Categories]="TOTALS"),FILTER(Categories_US_Final,Categories_US_Final[Year]="2020")),

CALCULATE(SUM('Raw Data'[Budget]),FILTER('Raw Data','Raw Data'[Year]=2020))/1000000

)

Showing error saying expressions refers to multiple columns.

Basically I want to filter out data:

When Market Tier Budget location = T40 (Incl US) then

            1. Need to sum Budget from Raw data and filter of year 2020 

Add

           2. TOTALS from Categories_US_FINAL[TOTAL] and filter of year 2020

Else only Need to sum Budget from Raw data and filter of year 2020 

Kindly suggest.

[AlexisOlson · Accepted solution]

Your first argument in your SWITCH is a table. It needs to be a single scalar value.

I'd suggest reworking your measure like this:

Measure1 =
VAR Budget =
    CALCULATE (
        SUM ( 'Raw Data'[Budget] ),
        KEEPFILTERS ( 'Raw Data'[Year] = 2020 )
    ) / 1000000
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Raw Data'[Market Tier - Budget location] ),
        "T40 (Incl US)",
            CALCULATE (
                SUM ( Categories_US_Final[TOTAL] ),
                KEEPFILTERS ( Categories_US_Final[Categories] = "TOTALS" ),
                KEEPFILTERS ( Categories_US_Final[Year] = "2020" )
            ) + Budget,
        Budget
    )

[Anonymous]

Thank a lot for quick response. It's helpful.