cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JennS
Frequent Visitor

Switch true () to cascade through a list of skills for each distinct associate

I have a table that has a list of associates id's and then a column that provides a list of skills that are assigned to them. The table is formatted as below. How do I get the switch to cascade through the list of skils for each TSR by specific order. For example if they have the code JMSR, they are senior and can switch to true, but if they don't have that skill, I need it to continue to go to the next of JMOV, then on to JMUN..etc.

 

I am trying to grab the list of distinct associates, for this list go through the list of skills and assign the first or highest skill to the tsr in a new colum

 

I am using the below for switch true but can't get it by the tsr level.
Switch(
TRUE(),
<table_name>[ts_skill] = 'JMSR', "Senior"
)

 

ts_tsrts_skill
JMMDJMSR

JMMD

JMOV
JMMDJMUN
JMXXJMOV
JMXXJMUN

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

Grab the skills for the particular tsr and then use SWITCH ( TRUE(), ... )

 

overall = 
VAR skills =
    CALCULATETABLE (
        VALUES ( Assoc[ts_skill] ),
        ALLEXCEPT ( Assoc, Assoc[ts_tsr] )
    )
RETURN
    SWITCH (
        TRUE (),
        "JMSR" IN skills, "Senior",
        "JMOV" IN skills, "Over",
        "Under"
    )

View solution in original post

6 REPLIES 6
AlexisOlson
Super User
Super User

Grab the skills for the particular tsr and then use SWITCH ( TRUE(), ... )

 

overall = 
VAR skills =
    CALCULATETABLE (
        VALUES ( Assoc[ts_skill] ),
        ALLEXCEPT ( Assoc, Assoc[ts_tsr] )
    )
RETURN
    SWITCH (
        TRUE (),
        "JMSR" IN skills, "Senior",
        "JMOV" IN skills, "Over",
        "Under"
    )

View solution in original post

This worked perfectly!!! You are awesome thank you so much for the help!!! 

AlexisOlson
Super User
Super User

What is the output/result that you are looking to get?

I am looking to get just one overall skill for each TSR. So if they have the code JMSR for Senior and have 4 other skill codes since Senior is my highest level, that TSR would be coded Senior. If the TSR doesn't have JMSR then check for JMOV and assign Over and so on. I just want to assign an Overall skill starting at Senior then Over then Under. Each TSR has multiple skills and I need to assign them the highest skill they have listed. 

So a calculated column like this?

ts_tsrts_skilloverall
JMMDJMSRSenior

JMMD

JMOVSenior
JMMDJMUNSenior
JMXXJMOVOver
JMXXJMUNOver

 

Or are you trying to write a measure? If you want a measure, does the filter context include ts_skill?

Yes just like the calculated column, that's exactly what I need. Am I able to do this with the switch true?

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors