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
AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
5 years ago

This is probably a good use case for Calculation Groups but you can probably get away with those with some SWITCH functions.

 

Something like this perhaps:

Sales LY =
VAR SalesType = SELECTEDVALUE ( 'Sales Type'[Sales Type] )
VAR PeriodType = SELECTEDVALUE ( 'Period Type'[Period Type] )
VAR PeriodDates =
    SWITCH (
        PeriodType,
        "LY", SAMEPERIODLASTYEAR ( 'Date'[Date] ),
        "YTD", DATESYTD ( 'Date'[Date] )
        /* Add more cases as desired. */
    )
RETURN
    CALCULATE (
        SWITCH (
            SalesType,
            "Contract Sales", SUM ( 'SAP Sales Daily'[Contract_Sales] ),
            "Invoice Sales", - SUM ( 'SAP Sales Daily'[Gross_Sales] ),
            "WAC Sales", SUM ( 'SAP Sales Daily'[Wac_Sales] )
        ),
        PeriodDates
    )
  • 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?