Adding DAX Variables to a table in a measure to create a count

[Richard_Halsall]

Hi, I have been struggling with this DAX

I have 2 tables

Technician which has name & job role (this job role has multiple roles seperated with a colon)

Job Role which is a disconnected table containing the unique job roles

The disconnected table Job Role is used as a slicer, and I have the measure below which flags if a technician name has the selected slicer job role/s in their job role field

RoleCountRHTest =

VAR _a =

    SELECTCOLUMNS ( 'Technician Roles', "Slicer", [Technician Role] )

VAR _b =

    ADDCOLUMNS (

        _a,

        "Count",

            SWITCH (

                TRUE (),

                ISBLANK ( MAX ( 'DimContractor'[Job_Title__c] ) )

                    && [Slicer] = "No Role", 1,

                MAX ( 'DimContractor'[Job_Title__c] ) <> BLANK ()

                    && CONTAINSSTRING ( MAX ( 'DimContractor'[Job_Title__c] ), [Slicer] ), 1

            )

    )

RETURN

    IF (

        SUMX ( _b, [Count] )>=1

            || NOT ( ISFILTERED ( 'Technician Roles'[Technician Role] ) ),

        1,

        0

    )

This works fine in a table visual with the technician name and measure

What I really need now is the measure above to be amended to calculate a count of those technicians where the RoleCountRHTest = 1 so I can show this in a card visual which will show the total number of technicians with the job roles selected in the disconnected slicer

Any help much appreciated. Thanks

[Daniel29195]

why dont you expand the first table on rows, and then you can link table 2 to table 1  as  1-many relationship 

on job . 

wouldnt it ease your work ? 

[Richard_Halsall]

Hi that wouldn't be possible Technician table has structure below:

Technician Name	Job Role
Dave	Blade Tech 1;Rope Tech 3
John	Blade Tech 3

Disconnected table Job Role has structure below:

Job Role

Blade Tech 1

Blade Tech 3

Rope Tech 3

[Daniel29195 · Accepted solution]

you can with power query transfom the first table from : 

to 

[Richard_Halsall]

Thanks although that is a solution I would like to resolve in DAX rather than creating another table in the model, the model has far more breadth than the 2 tables I have cited in the example