Forum Discussion

a119526's avatar
a119526
Frequent Visitor
5 years ago

Switch statement with two independent selected values

Hi -    I am putting together a PVM analysis that currently compares Year-Over-Year variances for certain products. I want to add an additional selection box (similar to Sales Type in the top right...
Need Help
a119526's avatar
a119526
Frequent Visitor
5 years ago

Thanks for  your response Alex.

 

I put together the following DAX, but am getting an error. 

"The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column."

Sales LY test =

VAR SelectionSales = SELECTEDVALUE('Sales Type'[Sales Type])
VAR DateType = SELECTEDVALUE('Date Type'[Date Type])
VAR Dates =
SWITCH(DateType,
"YTD",ENDOFYEAR(PREVIOUSYEAR('Date'[Date])),
"MoM",PREVIOUSMONTH('Date'[Date]),
"YoY",SAMEPERIODLASTYEAR('Date'[Date])
)

RETURN
CALCULATE(
SWITCH(SelectionSales,
"Contract Sales",CALCULATE(SUM('SAP Sales Daily'[Contract_Sales])),
"Invoice Sales",CALCULATE(-SUM('SAP Sales Daily'[Gross_Sales])),
"WAC Sales", CALCULATE(SUM('SAP Sales Daily'[Wac_Sales])))
,Dates)
 
AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
5 years ago

Hmm. It looks like it isn't recognizing the Dates variable as a table argument. Does it help at all if you wrap each of the Dates cases with CALCULATETABLE, e.g. "MoM", CALCULATETABLE ( PREVIOUSMONTH ( 'Date'[Date ) ), and/or move the Dates argument inside each SelectionSales case instead of the outside?

  • a119526's avatar
    a119526
    Frequent Visitor
    5 years ago

    Unfortunately neither worked. 

     

    The top section before the RETURN works as expected, but the second SWITCH does not want to work with the 'DATES' filter. I also tried creating a different measure and using that as a filter, that didn't work either.

     

    I wonder if I can do a series of IF statements? 

    • a119526's avatar
      a119526
      Frequent Visitor
      5 years ago

      Ok, I think I figured it out. Probably not the most elegant solution, but it works.

      Sales LY test = 

VAR SelectionSales = SELECTEDVALUE('Sales Type'[Sales Type])
VAR DateType = SELECTEDVALUE('Date Type'[Date Type])

RETURN
IF(DateType = "YTD",
        SWITCH(SelectionSales,
        "Contract Sales",CALCULATE(SUM('SAP Sales Daily'[Contract_Sales]),ENDOFYEAR(PREVIOUSYEAR('Date'[Date]))),
        "Invoice Sales",CALCULATE(-SUM('SAP Sales Daily'[Gross_Sales]),ENDOFYEAR(PREVIOUSYEAR('Date'[Date]))),
        "WAC Sales", CALCULATE(SUM('SAP Sales Daily'[Wac_Sales]),ENDOFYEAR(PREVIOUSYEAR('Date'[Date])))),
IF(DateType = "MoM",
        SWITCH(SelectionSales,
        "Contract Sales",CALCULATE(SUM('SAP Sales Daily'[Contract_Sales]),ENDOFMONTH(PREVIOUSMONTH('Date'[Date]))),
        "Invoice Sales",CALCULATE(-SUM('SAP Sales Daily'[Gross_Sales]),ENDOFMONTH(PREVIOUSMONTH('Date'[Date]))),
        "WAC Sales", CALCULATE(SUM('SAP Sales Daily'[Wac_Sales]),ENDOFMONTH(PREVIOUSMONTH('Date'[Date])))),
IF(DateType = "YoY",
        SWITCH(SelectionSales,
        "Contract Sales",CALCULATE(SUM('SAP Sales Daily'[Contract_Sales]),ENDOFMONTH(SAMEPERIODLASTYEAR('Date'[Date]))),
        "Invoice Sales",CALCULATE(-SUM('SAP Sales Daily'[Gross_Sales]),ENDOFMONTH(SAMEPERIODLASTYEAR('Date'[Date]))),
        "WAC Sales", CALCULATE(SUM('SAP Sales Daily'[Wac_Sales]),ENDOFMONTH(SAMEPERIODLASTYEAR('Date'[Date]))))
)))
      • AlexisOlson's avatar
        AlexisOlson
        Icon for Super User rankSuper User
        5 years ago

        Yeah, worst case you have to define all 9 possibilities. I think I could get something like my initial suggestion working but it would take some tinkering.

         

        If you do go with 9 cases, you can also do it without nesting like this:

        Sales LY test =
VAR SelectionSales = SELECTEDVALUE ( 'Sales Type'[Sales Type] )
VAR DateType = SELECTEDVALUE ( 'Date Type'[Date Type] )
RETURN
    SWITCH (
        TRUE (),
        DateType = "YTD" && SelectionSales = "Contract Sales",
            CALCULATE (
                SUM ( 'SAP Sales Daily'[Contract_Sales] ),
                ENDOFYEAR ( PREVIOUSYEAR ( 'Date'[Date] ) )
            ),
        DateType = "YTD" && SelectionSales = "Invoice Sales",
            CALCULATE (
                - SUM ( 'SAP Sales Daily'[Gross_Sales] ),
                ENDOFYEAR ( PREVIOUSYEAR ( 'Date'[Date] ) )
            ),
        DateType = "YTD" && SelectionSales = "WAC Sales",
            CALCULATE (
                SUM ( 'SAP Sales Daily'[Wac_Sales] ),
                ENDOFYEAR ( PREVIOUSYEAR ( 'Date'[Date] ) )
            ),
        DateType = "MoM" && SelectionSales = "Contract Sales",
            CALCULATE (
                SUM ( 'SAP Sales Daily'[Contract_Sales] ),
                ENDOFMONTH ( PREVIOUSMONTH ( 'Date'[Date] ) )
            ),
        DateType = "MoM" && SelectionSales = "Invoice Sales",
            CALCULATE (
                - SUM ( 'SAP Sales Daily'[Gross_Sales] ),
                ENDOFMONTH ( PREVIOUSMONTH ( 'Date'[Date] ) )
            ),
        DateType = "MoM" && SelectionSales = "WAC Sales",
            CALCULATE (
                SUM ( 'SAP Sales Daily'[Wac_Sales] ),
                ENDOFMONTH ( PREVIOUSMONTH ( 'Date'[Date] ) )
            ),
        DateType = "YoY" && SelectionSales = "Contract Sales",
            CALCULATE (
                SUM ( 'SAP Sales Daily'[Contract_Sales] ),
                ENDOFMONTH ( SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
            ),
        DateType = "YoY" && SelectionSales = "Invoice Sales",
            CALCULATE (
                - SUM ( 'SAP Sales Daily'[Gross_Sales] ),
                ENDOFMONTH ( SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
            ),
        DateType = "YoY" && SelectionSales = "WAC Sales",
            CALCULATE (
                SUM ( 'SAP Sales Daily'[Wac_Sales] ),
                ENDOFMONTH ( SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
            )
    )