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
Super 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] ) )
            )
    )
a119526's avatar
a119526
Frequent Visitor
5 years ago

Thanks so much for this. 

Just curious, would this solution perform better than the nested IF statements? They both give the same answers.

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    5 years ago

    I'd expect them to be about the same but can't say for sure.

     

    The reason I'd consider it has more to do with the fact that nested logic tends to be a bit more difficult to read, in general. I prefer to not be mentally matching parentheses if I don't have to.