Is it possible to use a switch within a switch for a measure?

[Anonymous]

Hi there, I have a measure here called "Commission", and this measure determines a profit % depending on the employee's name. However, I want to be able to make it more advanced, rather than it look for an employees name, I want it to also check the current sales item.

Here is the current measure's code below:

Commission =
VAR __employee = SELECTEDVALUE ( 'Employee' [Employee Name] )
VAR __rate =
SWITCH ( __employee,
"Employee 1", 10,
"Employee 2", 20,
"Employee 3", 30,
10 //default rate
)
RETURN
'Sales '[Profit] / 100 * __rate

This measure currently works perfectly, it will scan an employee name then determine what % that employee is getting. However, I want to also scan the sales item. 

Here is an example of what I'm aiming for below:

Commission =
VAR __employee = SELECTEDVALUE ( 'Employee' [Employee Name] )
VAR __saleitem = SELECTEDVALUE ( 'Items' [Item Type] )
VAR __rate =
SWITCH ( __employee,
"Employee 1", 10, SWITCH ( __saleitem,
"Hardware", 15, "Software", 20),
"Employee 2", 20,
"Employee 3", 30,
10 //default rate
)
RETURN
'Sales '[Profit] / 100 * __rate​

This example above produces no errors, however, the visual cannot be displayed (card visual) so it's not working for whatever reason. 

Is there a way to implement this? Or perhaps you guys can offer other suggestions? 

[selimovd · Accepted solution]

Hey @Anonymous ,

you didn't remove the 10 for the first employee. So the case and result statements are mixed up. Try it like this:

Commission =
VAR __employee = SELECTEDVALUE( 'Employee'[Employee Name] )
VAR __saleitem = SELECTEDVALUE( 'Items'[Item Type] )
VAR __rate =
    SWITCH(
        __employee,
        "Employee 1",
            SWITCH(
                __saleitem,
                "Hardware", 15,
                "Software", 20
            ),
        "Employee 2", 20,
        "Employee 3", 30,
        10 //default rate
    )
RETURN
    'Sales '[Profit] / 100 * __rate​

As your second switch is not really depending on the output of the first switch, you could also put it as an independent block of code. This would make it a little bit more readable:

Commission =
VAR __employee = SELECTEDVALUE( 'Employee'[Employee Name] )
VAR __saleitem = SELECTEDVALUE( 'Items'[Item Type] )
VAR __switchSaleitem =
    SWITCH(
        __saleitem,
        "Hardware", 15,
        "Software", 20
    )
VAR __rate =
    SWITCH(
        __employee,
        "Employee 1", __switchSaleitem,
        "Employee 2", 20,
        "Employee 3", 30,
        10 //default rate
    )
RETURN
    'Sales '[Profit] / 100 * __rate​

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Hi @selimovd thanks for your help. 

However, I did prefer your 2nd option as it's better coding but for some reason it didn't work. it displayed a visual error on the card and I'm not sure why?