Help me with the data where I need to provide Classification against Skill Description ?

[ashmitp869]

Hi,
I need some help with my data—help me to achieve this.
I have a data set with skill descriptions, which I need to classify.

i.e if [Skill Description] data contains any of the Role from the Role table then
it a Role.
Else look for Actvity table 

eg,
Else [Ticket] Table.

Expected Results 

Skill Description	Classification
001 - Expanda Profile Handler	Role
002 - CCTV Operator (Non WinCam)	Role
008 - Retic Expanda Console Operator	Role
008 - Retic Expanda Console Operator	Role
009 - Retic Expanda Down Hole Operator	Role
010 - Retic Cutter Operator	Role
014 - Carriers Expanda Down Hole Operator	Role
016 - Top Hat Operator	Role
018 - Grouting Pump Operator	Role
021 - Rotaloc Crew Leader	Role
022 - General Labour Duties	Role
022 - General Labour Duties	Role
022 - General Labour Duties	Role
022 - General Labour Duties	Role
022 - General Labour Duties (Job Role 022)	Role
024 - Rotaloc Winder Operator	Role
050 - Site Management	Role
056 - HD Profiler Operator	Role
081 - Interpatch Operator	Role
082 - Interpatch Offsider	Role
116 - CIPP Operator (Hot Water Cure)	Role
119 - CIPP Operator (UV Cure)	Role
120 - CIPP Offsider (UV Cure)	Role
Above The Water Line Session	Ticket
Above The Water Line Session	Ticket
Access Cavern Project Induction	Ticket
Access Cavern Project Induction	Ticket
Articulated Haul Truck - RIIMPO337D	Ticket
Articulated Haul Truck - RIIMPO337D	Ticket
Asbestos Awareness - HR Online Induction	Ticket
Asbestos Awareness - HR Online Induction	Ticket
Basic Scaffolding Course	Ticket
Basic Scaffolding Course	Ticket
Boiler Operator	Ticket
Brokk Operator Card	Ticket
Brokk Operator Card	Ticket
Brokk VOC Certificate	Ticket
Brokk VOC Certificate	Ticket
C6 Hiab Crane Training	Ticket
C6-Slewing Mobile Crane Course	Ticket
Certificate III in Civil Construction	Ticket
Certificate III in Drilling Operations RII31820	Ticket
Certificate III in Drilling Operations RII31820	Ticket
Closed Timber Shoring	Ticket
Confined Space - Initial	Ticket
Confined Space - Refresher	Ticket
Confined Space - Refresher	Ticket
Confined Space Medical	Ticket
Confined Space Medical	Ticket
Confined Space Medical	Ticket
Control electrical risk on Metallic Pipes	Ticket
Crew Leader Leadership Program	Ticket
Crystalline Silica Exposure- ACT	Ticket
Crystalline Silica Exposure- ACT	Ticket
Cyber Security Foundations	Ticket
Cyber Security Foundations	Ticket
Cyber Security Foundations (ITSEC01)	Ticket
Cyber Security Foundations (ITSEC01)	Ticket
Dogging Course	Ticket
Dogging Course	Ticket
Drivers Licence-HR	Ticket
Drivers Licence-HR	Ticket
Electrical Test & Tagging	Ticket
Excavator Ticket	Ticket
Excavator Ticket	Ticket
First Aid Training	Ticket
First Aid Training	Ticket
Front End Loader Operations RIIMPO321	Ticket
Front End Loader Operations RIIMPO321	Ticket
Front End Loader Skid Steer course - RIIMPO318	Ticket
Frontline Leaders Program	Ticket
Hammelmann HDP120 with MH Cleaning Equipment	Ticket
Harm 2 Zero - HSE Leadership	Ticket
Harm 2 Zero - HSE Leadership	Ticket
Height Safety	Ticket
Height Safety	Ticket
High Pressure Water Jetting - Operate a drain cleaning system - MSM05/019	Ticket
High Pressure Water Jetting - Operate a high pressure water jetting system - MSM04/018	Ticket
High Pressure Water Jetting - Operate a vacuum loading system - MSM06/020	Ticket
High Pressure Water Jetting - Use high pressure water jetting system equipment - MSM03/017	Ticket
High Pressure Water Jetting - Use high pressure water jetting system equipment - MSM03/017	Ticket
High Risk Work Course (Licence goes in Licences Section	Ticket
HR Drivers Training Course	Ticket
Induction - Asbestos Awareness	Ticket
Induction - ATV (Utility Vehicle)	Ticket
Induction - ATV (Utility Vehicle)	Ticket
Induction - Avoid Fail Worker	Ticket
Induction - Avoid Fail Worker	Ticket
Induction - Civil Construction Field Worker	Ticket
Induction - Commuter Bus	Ticket
Induction - Commuter Bus	Ticket
Induction - High Tip Wheelbarrow	Ticket
Induction - High Tip Wheelbarrow	Ticket
Install Trench Support - RIICCM210D	Ticket
Interflow's HSE Induction	Ticket
Interflow's HSE Induction	Ticket
Interflow's HSE Life Saving Rules	Ticket
Interflow's HSE Life Saving Rules	Ticket
Interflow's Respect @ Work	Ticket
Isolate and access plant - RIISAM202	Ticket
Isolate and access plant - RIISAM202	Ticket
Laser Safety Awareness	Ticket
Manual Handling Induction	Ticket
Manual Handling Induction	Ticket
Maxilift ML180.3 Crane Handover	Ticket
Maxilift ML180.3 Crane Handover	Ticket
Our Interflow Behaviours	Ticket
Phishing and Anti-Spam Software	Ticket
Phishing and Anti-Spam Software	Ticket
PPE - All Worker Induction and Assessment	Ticket
PPE - Field Worker Induction and Assessment	Ticket
Rail Card – RIW	Ticket
Rail Card – RIW	Ticket
Rail Cat3 Medical	Ticket
Rail Cat3 Medical	Ticket
RAX500 - DX5000 500 Metre Cable Reel Kit	Ticket
Respirator Fit Test	Ticket
Safely Access the Rail Corridor - TLIF0020	Ticket
Safely Access the Rail Corridor - TLIF0020	Ticket
Silica Awareness Training	Ticket
Silica Awareness Training	Ticket
Silica Medical Chest Xray	Ticket
Silica Medical Chest Xray	Ticket
Spool Lifting Spreader Beam	Ticket
Spotters Certificate	Ticket
Spotters Certificate	Ticket
SW Access Card – Avoid Fail	Ticket
SW Access Card – Avoid Fail	Ticket
Sydney Water – Flow Management	Ticket
Sydney Water – Flow Management	Ticket
Task How-2 - Pre-start Meetings	Ticket
Task How-2 - Use a Grout Mixer and Pump	Ticket
Telescopic Material Handler	Ticket
Telescopic Material Handler	Ticket
Traffic Control - (stop/slow bat) and Implement Traffic Plan	Ticket
Traffic Control - Select/modify traffic plans	Ticket
Traffic Control - Set out signs	Ticket
Traffic Control - Set out signs	Ticket
Traffic Controller (stop/slow person)	Ticket
Union Right of Entry Training	Ticket
Union Right of Entry Training	Ticket
Unique Student Identifier	Ticket
Unique Student Identifier	Ticket
Vehicle Induction - General	Ticket
Vehicle Induction - General	Ticket
VOC 0003K Steel Wheeled Roller Operations (VOC_0003)	Activity
VOC 0004K Skid Steer Loader with Attachments (VOC_0004)	Activity
VOC 0004K Skid Steer Loader with Attachments (VOC_0004)	Activity
VOC 0006 Vehicle loading crane operations PO	Activity
VOC 0006K Vehicle Loading Crane Operations (VOC_0006)	Activity
VOC 0006K Vehicle Loading Crane Operations (VOC_0006)	Activity
VOC 0007 Mini loader operations – PO	Activity
VOC 0007K Mini Loader Operations (VOC_0007)	Activity
VOC 0007K Mini Loader Operations (VOC_0007)	Activity
VOC 0008K Backhoe Operations (VOC_0008)	Activity
VOC 0009 Forklift operations – PO	Activity
VOC 0009 Forklift operations – PO	Activity
VOC 0009K Forklift Operations (VOC_0009)	Activity
VOC 0009K Forklift Operations (VOCK_0009)	Activity
VOC 0010 Excavator operations – KA	Activity
VOC 0010K Excavator Operations (VOC_0010)	Activity
VOC 0011K Dogging Operations (VOC_0011)	Activity
VOC 0011K Dogging Operations (VOC_0011)	Activity
VOC 0012K Tipper Operations (VOC_0012)	Activity
VOC 0012K Tipper Operations (VOC_0012)	Activity
VOC 0014 - Hook Loader Truck - PO	Activity
VOC 0014K Hook Loader Truck (VOC_0014)	Activity
VOC 0014K Hook Loader Truck (VOC_0014)	Activity
VOC 0015K Gantry Crane Operations (VOC_0015)	Activity
VOC 0015K Gantry Crane Operations (VOC_0015)	Activity
VOC 0018K Telehandler (VOC_0018)	Activity
VOC 0018K Telehandler (VOC_0018)	Activity
VOC 0019 Walkie stacker PO	Activity
VOC 0019K Walkie Stacker (VOC_0019)	Activity
VOC 0019K Walkie Stacker (VOC_0019)	Activity
VOC 0020 Angle grinder KA	Activity
VOC 0020 Angle grinder KA	Activity
VOC 0020 Angle grinder PO	Activity
VOC 0020 Angle grinder PO	Activity
VOC 0021 Demolition saw KA	Activity
VOC 0021 Demolition saw KA	Activity
VOC 0022 Jackhammer KA	Activity
VOC 0022 Jackhammer KA	Activity
VOC 0022 Jackhammer PO	Activity
VOC 0023 Chainsaw KA	Activity
VOC 0023K Chainsaw (VOC_0023)	Activity
VOC 0024 Hand drill KA	Activity
VOC 0024 Hand drill KA	Activity
VOC 0024 Hand drill PO	Activity
VOC 0024 Hand drill PO	Activity
VOC 0025 Circular saw KA	Activity
VOC 0025 Circular saw KA	Activity
VOC 0025 Circular saw PO	Activity
VOC 0025 Circular saw PO	Activity
VOC 0026 Reciprocating saw KA	Activity
VOC 0026 Reciprocating saw PO	Activity
VOC 0026 Reciprocating saw PO	Activity
VOC 0026K Reciprocating Saw (VOC_0026)	Activity
VOC 0029K Core Drill With Mounting Rig (VOC 0029)	Activity
VOC 0030 Hydraulic Chainsaw – KA	Activity
VOC 0030 Hydraulic Chainsaw – PO	Activity
VOC 0030 Hydraulic Chainsaw – PO	Activity
VOC 0030K Hydraulic Chainsaw (VOC_0030)	Activity
VOC 0031K Spotting for Below and Aboveground Assets (VOC_0031)	Activity
VOC 0034K Telehandler - Merlo (VOC 0034)	Activity
White Card	Ticket
White Card	Ticket
White Card	Ticket

Sample file provided
https://github.com/suvechha/samplepbi/blob/main/sample_data.pbix

[Irwan]

hello @ashmitp869 

for the Expanda Profile Handler, how to achive that classification value?

i dont see the same or contained skill in Role tble.

Thank you.

[ashmitp869]

So if [Skill Description] =

• 002 - CCTV Operator (Non WinCam) as Role
• Unique Student Identifier as Ticket
• Any Skill Description containing "VOC" as Activity
• Any Skill Description containing "License" or "Licence" as License
• All others as Ticket

[ashmitp869 · Accepted solution]

I found the Classification

Classification =

VAR Prefix = LEFT([Skill Description], 3)

VAR PrefixNum =

    IFERROR(VALUE(Prefix), BLANK())

VAR IsPrefixNumber =

    IF(

        NOT(ISBLANK(PrefixNum)) &&

        PrefixNum >= 1 &&

        PrefixNum <= 111,

        TRUE(),

        FALSE()

    )

RETURN

    IF(

        IsPrefixNumber,

        "Role",

        IF(

            SEARCH("VOC", [Skill Description], 1, 0) > 0,

            "Activity",

            IF(

                SEARCH("License", [Skill Description], 1, 0) > 0 ||

                SEARCH("Licence", [Skill Description], 1, 0) > 0,

                "License",

                "Ticket"

            )

        )

    )

[Irwan]

hello @ashmitp869 

please check if this accomodate your need.

if you want to use measure, then this measure DAX

Classification Measure =
var _Number = {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}
var _String = LEFT(SELECTEDVALUE('Data'[Skill Description]),3)
var _Mid = MID(_String,1,1)
var _Left = LEFT(_String,1)
var _Right = RIGHT(_String,1)
Return
IF(
    _Mid IN _Number ||_left IN _Number ||_right IN _Number,
    "Role",
IF(
    CONTAINSSTRING(SELECTEDVALUE('Data'[Skill Description]),"VOC "),
    "Activity",
IF(
    CONTAINSSTRING(SELECTEDVALUE('Data'[Skill Description]),"License"),
    "License",
    "Ticket"
)))

if you want to use column, then this column DAX

Classification Column =
var _Number = {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}
var _String = LEFT('Data'[Skill Description],3)
var _Mid = MID(_String,1,1)
var _Left = LEFT(_String,1)
var _Right = RIGHT(_String,1)
Return
IF(
    _Mid IN _Number ||_left IN _Number ||_right IN _Number,
    "Role",
IF(
    CONTAINSSTRING('Data'[Skill Description],"VOC "),
    "Activity",
IF(
    CONTAINSSTRING('Data'[Skill Description],"License"),
    "License",
    "Ticket"
)))

Hope this will help.

Thank you.

[ryan_mayu]

@ashmitp869 

for the first one 001 - Expanda Profile Handler, I didn't see this match the data in the role table. why its classification is role?