Multiple selection from slicer with multiple values in column

[Richard_Halsall]

Hi all

Struggling to write a correct DAX measure to return a value of 1 in the following scenario

I have a disconnected table (Technician Roles) of unique job roles which is the data for the slicer

Role	Sort
Role 1	1
Role 2	2
Role 3	3

I have a DimTechnicians table with a role field (for which they may have multiple)

Id	Name	Role
1	Person 1	Role 1
2	Person 2	Role 2; Role 3
3	Person 3	Role 3
4	Person 4	Role 3; Role 1; Role 3

I then have a Fact table at the technician ID level on which I have based a table visual

Single select of the slicer works fine, multiple select doesn't

I have written this measure which I place on the table visual as a filter

To filter = SEARCH(SELECTEDVALUE('Technician Roles'[Technician Role]),SELECTEDVALUE(DimTechnicians[Job_Title]),,-1)

I have also tried this

ToFilter1 =

Var textselect=selectedvalue('Technician Roles'[Technician Role])

var textfinal=SELECTEDVALUE('DimTechnicians'[Job_Title])

return

if(CONTAINSSTRINGEXACT(textfinal,textselect),1)

Neither measure works

So for multiple slicer selection, using the example above, I would want to return technician id's who have Blade Tech 2 or Service Tech 4 in their Job Title

Any help would ba appreciated. Thanks

[DallasBaba · Accepted solution]

 @Richard_Halsall can try below measure. It will checks if the technician's role field contains any of the selected roles. If so, it returns 1, otherwise 0.

ToFilter = 
VAR SelectedRoles = CONCATENATEX(VALUES('Technician Roles'[Role]), 'Technician Roles'[Role], ";")
VAR TechnicianRoles = DimTechnicians[Role]
RETURN
IF (
    NOT(ISBLANK(TechnicianRoles)) && TechnicianRoles <> "",
    IF (
        LEN(SelectedRoles) > 0 && 
        COUNTROWS(
            FILTER(
                VALUES('Technician Roles'[Role]),
                CONTAINSSTRING(TechnicianRoles, 'Technician Roles'[Role])
            )
        ) > 0,
        1,
        0
    ),
    0
)

If this don't work, kindly @me and upload a sample of the pbix file you are working with for easy debugging. 

[Richard_Halsall]

@DallasBaba Many thanks worked a treat