How can I create one slicer to filter a column containing upto three values separated by delimeter

[HassanAshas]

I have a dataset like following, 

Emp Code	Name	Skill
1	Matt	AWS, Python, Django
2	Avery	AWS
3	Aryub	AWS, Python, JavaScript
4	Jacob	Azure, NodeJS
5	Zara	NodeJS, React
6	Ali	Azure, Python, PySpark
7	John	PySpark
8	Alisha
9	Novak	Python
10	Alex	Django
11	Emma	JavaScript, React, NodeJS

I want to create one slicer which will have distinct values for the "Skill" Column. I have a Table which simply shows the same table, like this, 

I want to select the Distinct value from the Slicer and then filter whereever that Skill is present. For example, if I filter by "Python", then it should show me the Employees with Emp ID: 1, 3, 6, and 9. 

Similarly, if I filter by Django, then it should show Emp ID: 1 and 10. 

How can I achieve this? 

I know one possible way is to duplicate the rows and create as many rows as the number of skills are for each employee (by unpivotting table). So, for example, for Emp ID 1, I will have three different rows, but this approach will badly increase the data model size. 

Is there any other way to solve this? Thank you. 

You can download the Sample Power BI File from here: https://drive.google.com/file/d/1AFCPRLmGrNLAHjgsFL-dEqesAoNdLUQ5/view?usp=sharing

[grazitti_sapna]

Hey try using the below steps

Duplicate the table and split the column at the row level in the power query editor

Name the duplicate table as skill slicer and change the cross-filter direction to both.

Use the skill from the skill slicer table in the slicer to filter the data in the skill table.

[HassanAshas]

Hi @grazitti_sapna 

Don't you think bidirectional filtering is a bad practice? Is there any alternative approach than using the bidirectional filtering? 

[HassanAshas]

Hi @grazitti_sapna 

Don't you think bidirectional filtering is a bad practice? Is there any alternative approach than using the bidirectional filtering? 

[v-jianboli-msft · Accepted solution]

Hi @HassanAshas ,

Please try:

First create a new table with Skill column:

Then split columns:

Then create slicer with the new table:

Note: These two tables do not have any relationship.

Then apply the measure to the visual level filter:

Flag =
VAR _a =
    ADDCOLUMNS (
        VALUES ( 'For slicer'[Skill] ),
        "flag",
            SWITCH (
                TRUE (),
                [Skill] = BLANK ()
                    && SELECTEDVALUE ( Data[Skill] ) = BLANK (), 1,
                [Skill] <> BLANK ()
                    && CONTAINSSTRING ( SELECTEDVALUE ( Data[Skill] ), [Skill] ), 1,
                0
            )
    )
VAR _b =
    SUMX ( _a, [flag] )
VAR _c =
    DISTINCTCOUNT ( 'For slicer'[Skill] )
RETURN
    SWITCH (
        TRUE (),
        NOT ( ISFILTERED ( 'For slicer'[Skill] ) ), 1,
        ISFILTERED ( 'For slicer'[Skill] )
            && _b = _c, 1
    )

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GP14]

Jianbo this is brilliantly clear answer to a very complex issue that I've been trying to resolve for some time, thank you. 

[Tracy_cai]

Hi@v-jianboli-msft

Could you help me? I'm trying to put this measure into the card visual, but I can't filter flag=1, can you tell me how to solve this problem? Thank you so much