Forum Discussion

ashmitp869's avatar
ashmitp869
Icon for Responsive Resident rankResponsive Resident
1 year ago
Solved

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

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 DescriptionClassification
001 - Expanda Profile HandlerRole
002 - CCTV Operator (Non WinCam)Role
008 - Retic Expanda Console OperatorRole
008 - Retic Expanda Console OperatorRole
009 - Retic Expanda Down Hole OperatorRole
010 - Retic Cutter OperatorRole
014 - Carriers Expanda Down Hole OperatorRole
016 - Top Hat OperatorRole
018 - Grouting Pump OperatorRole
021 - Rotaloc Crew LeaderRole
022 - General Labour DutiesRole
022 - General Labour DutiesRole
022 - General Labour DutiesRole
022 - General Labour DutiesRole
022 - General Labour Duties (Job Role 022)Role
024 - Rotaloc Winder OperatorRole
050 - Site ManagementRole
056 - HD Profiler OperatorRole
081 - Interpatch OperatorRole
082 - Interpatch OffsiderRole
116 - CIPP Operator (Hot Water Cure)Role
119 - CIPP Operator (UV Cure)Role
120 - CIPP Offsider (UV Cure)Role
Above The Water Line SessionTicket
Above The Water Line SessionTicket
Access Cavern Project InductionTicket
Access Cavern Project InductionTicket
Articulated Haul Truck - RIIMPO337DTicket
Articulated Haul Truck - RIIMPO337DTicket
Asbestos Awareness - HR Online InductionTicket
Asbestos Awareness - HR Online InductionTicket
Basic Scaffolding CourseTicket
Basic Scaffolding CourseTicket
Boiler OperatorTicket
Brokk Operator CardTicket
Brokk Operator CardTicket
Brokk VOC CertificateTicket
Brokk VOC CertificateTicket
C6 Hiab Crane TrainingTicket
C6-Slewing Mobile Crane CourseTicket
Certificate III in Civil ConstructionTicket
Certificate III in Drilling Operations RII31820Ticket
Certificate III in Drilling Operations RII31820Ticket
Closed Timber ShoringTicket
Confined Space - InitialTicket
Confined Space - RefresherTicket
Confined Space - RefresherTicket
Confined Space MedicalTicket
Confined Space MedicalTicket
Confined Space MedicalTicket
Control electrical risk on Metallic PipesTicket
Crew Leader Leadership ProgramTicket
Crystalline Silica Exposure- ACTTicket
Crystalline Silica Exposure- ACTTicket
Cyber Security FoundationsTicket
Cyber Security FoundationsTicket
Cyber Security Foundations (ITSEC01)Ticket
Cyber Security Foundations (ITSEC01)Ticket
Dogging CourseTicket
Dogging CourseTicket
Drivers Licence-HRTicket
Drivers Licence-HRTicket
Electrical Test & TaggingTicket
Excavator TicketTicket
Excavator TicketTicket
First Aid TrainingTicket
First Aid TrainingTicket
Front End Loader Operations RIIMPO321Ticket
Front End Loader Operations RIIMPO321Ticket
Front End Loader Skid Steer course - RIIMPO318Ticket
Frontline Leaders ProgramTicket
Hammelmann HDP120 with MH Cleaning EquipmentTicket
Harm 2 Zero - HSE LeadershipTicket
Harm 2 Zero - HSE LeadershipTicket
Height SafetyTicket
Height SafetyTicket
High Pressure Water Jetting - Operate a drain cleaning system - MSM05/019Ticket
High Pressure Water Jetting - Operate a high pressure water jetting system - MSM04/018Ticket
High Pressure Water Jetting - Operate a vacuum loading system - MSM06/020Ticket
High Pressure Water Jetting - Use high pressure water jetting system equipment - MSM03/017Ticket
High Pressure Water Jetting - Use high pressure water jetting system equipment - MSM03/017Ticket
High Risk Work Course (Licence goes in Licences SectionTicket
HR Drivers Training CourseTicket
Induction - Asbestos AwarenessTicket
Induction - ATV (Utility Vehicle)Ticket
Induction - ATV (Utility Vehicle)Ticket
Induction - Avoid Fail WorkerTicket
Induction - Avoid Fail WorkerTicket
Induction - Civil Construction Field WorkerTicket
Induction - Commuter BusTicket
Induction - Commuter BusTicket
Induction - High Tip WheelbarrowTicket
Induction - High Tip WheelbarrowTicket
Install Trench Support - RIICCM210DTicket
Interflow's HSE InductionTicket
Interflow's HSE InductionTicket
Interflow's HSE Life Saving RulesTicket
Interflow's HSE Life Saving RulesTicket
Interflow's Respect @ WorkTicket
Isolate and access plant - RIISAM202Ticket
Isolate and access plant - RIISAM202Ticket
Laser Safety AwarenessTicket
Manual Handling InductionTicket
Manual Handling InductionTicket
Maxilift ML180.3 Crane HandoverTicket
Maxilift ML180.3 Crane HandoverTicket
Our Interflow BehavioursTicket
Phishing and Anti-Spam SoftwareTicket
Phishing and Anti-Spam SoftwareTicket
PPE - All Worker Induction and AssessmentTicket
PPE - Field Worker Induction and AssessmentTicket
Rail Card – RIWTicket
Rail Card – RIWTicket
Rail Cat3 MedicalTicket
Rail Cat3 MedicalTicket
RAX500 - DX5000 500 Metre Cable Reel KitTicket
Respirator Fit TestTicket
Safely Access the Rail Corridor - TLIF0020Ticket
Safely Access the Rail Corridor - TLIF0020Ticket
Silica Awareness TrainingTicket
Silica Awareness TrainingTicket
Silica Medical Chest XrayTicket
Silica Medical Chest XrayTicket
Spool Lifting Spreader BeamTicket
Spotters CertificateTicket
Spotters CertificateTicket
SW Access Card – Avoid FailTicket
SW Access Card – Avoid FailTicket
Sydney Water – Flow ManagementTicket
Sydney Water – Flow ManagementTicket
Task How-2 - Pre-start MeetingsTicket
Task How-2 - Use a Grout Mixer and PumpTicket
Telescopic Material HandlerTicket
Telescopic Material HandlerTicket
Traffic Control - (stop/slow bat) and Implement Traffic PlanTicket
Traffic Control - Select/modify traffic plansTicket
Traffic Control - Set out signsTicket
Traffic Control - Set out signsTicket
Traffic Controller (stop/slow person)Ticket
Union Right of Entry TrainingTicket
Union Right of Entry TrainingTicket
Unique Student IdentifierTicket
Unique Student IdentifierTicket
Vehicle Induction - GeneralTicket
Vehicle Induction - GeneralTicket
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 POActivity
VOC 0006K Vehicle Loading Crane Operations (VOC_0006)Activity
VOC 0006K Vehicle Loading Crane Operations (VOC_0006)Activity
VOC 0007 Mini loader operations – POActivity
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 – POActivity
VOC 0009 Forklift operations – POActivity
VOC 0009K Forklift Operations (VOC_0009)Activity
VOC 0009K Forklift Operations (VOCK_0009)Activity
VOC 0010 Excavator operations – KAActivity
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 - POActivity
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 POActivity
VOC 0019K Walkie Stacker (VOC_0019)Activity
VOC 0019K Walkie Stacker (VOC_0019)Activity
VOC 0020 Angle grinder KAActivity
VOC 0020 Angle grinder KAActivity
VOC 0020 Angle grinder POActivity
VOC 0020 Angle grinder POActivity
VOC 0021 Demolition saw KAActivity
VOC 0021 Demolition saw KAActivity
VOC 0022 Jackhammer KAActivity
VOC 0022 Jackhammer KAActivity
VOC 0022 Jackhammer POActivity
VOC 0023 Chainsaw KAActivity
VOC 0023K Chainsaw (VOC_0023)Activity
VOC 0024 Hand drill KAActivity
VOC 0024 Hand drill KAActivity
VOC 0024 Hand drill POActivity
VOC 0024 Hand drill POActivity
VOC 0025 Circular saw KAActivity
VOC 0025 Circular saw KAActivity
VOC 0025 Circular saw POActivity
VOC 0025 Circular saw POActivity
VOC 0026 Reciprocating saw KAActivity
VOC 0026 Reciprocating saw POActivity
VOC 0026 Reciprocating saw POActivity
VOC 0026K Reciprocating Saw (VOC_0026)Activity
VOC 0029K Core Drill With Mounting Rig (VOC 0029)Activity
VOC 0030 Hydraulic Chainsaw – KAActivity
VOC 0030 Hydraulic Chainsaw – POActivity
VOC 0030 Hydraulic Chainsaw – POActivity
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 CardTicket
White CardTicket
White CardTicket



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

Need Help
  • ashmitp869's avatar
    ashmitp869
    1 year ago

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

5 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    1 year ago

    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?

  • Irwan's avatar
    Irwan
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      ashmitp869
      Icon for Responsive Resident rankResponsive Resident
      1 year ago

      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
      • Irwan's avatar
        Irwan
        Icon for Super User rankSuper User
        1 year ago

        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.