Forum Discussion

Richard_Halsall's avatar
Richard_Halsall
Helper IV
1 year ago
Solved

Multiple selection from slicer with multiple values in column

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

RoleSort
Role 11
Role 22
Role 33

 

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

IdNameRole
1Person 1Role 1
2Person 2Role 2; Role 3
3Person 3Role 3
4Person 4Role 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

 

Need Help
  • DallasBaba's avatar
    DallasBaba
    1 year ago

     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. 

     

2 Replies

  • DallasBaba's avatar
    DallasBaba
    Skilled Sharer
    1 year ago

     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.