Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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"
    )

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors