Filtering a table based on an exact match to a concatenated value

[MisterWolf]

Hi everybody and thanks in advance for your assistance. 

I have a single table called Staff that includes data from a Multi-Option Select. EG

ID	Name	Team Options
12314	Ed Mercer	Deals Direct With Friend
12345	Kelly Grayson	Deals Direact With Friend; Deals Direct With Third Party
23453	Bortus	Deals Direct With A Pal; Deals Direct With Third Party
234324	Claire Finn	Tends to Others
234234	John Lamarr	Deals Direct With Friend; Tends to Others
324324	Gordon Malloy	Deals Direct With Friend; Deals Direct With a Pal, Deals Direct With Third Party; Tends to Others
34323	Alara Kitan	Deals Direct With Third Party; Tends to Others
2433214	Talla Keyali
343245	Isaac	Deals Direct With A Pal; Deals Direct With a Third Party

Using the delimiter (semicolon), I split apart the Team Options column into a new column called Team Options List (creating additional rows in the Staff table), then created a slicer with the following choices from Teams Ooptions List:

(Blank)

Deals Direct With Friend

Deals Direct With A Pal

Deals Direct With Third Party

Tends to Others

I would like the table to return ONLY the values selected in the slicer.
EG:
1. Selecting nothing would return Talla Keyali.

2. Selecting Deals Direct With Friend and Deals Direct With Third Party would only return Kelly Grayson and Isaac.
3. Selecting all except (Blank) would only return Gordon Malloy

4. Selecting only Deals Direct With Friend would only return Ed Mercer

I've seen other examples that use Concatenatex and I can get a measure with a list in the correct order that matches the Team Options in the Staff Table, but I can't use the results of that measure to filter the table.

Here's the measure I created:

GMOSelected =

        CONCATENATEX(

            VALUES('Staff'[Team Options List]),'Staff'[Team Options List],

            "; ",

            'Staff'[Team Options List],

            ASC

        )

I can see the results of the measure coming out to show what I want, but I am hamstrung at this point. I've tried other suggestions but it hasn't been successful, so I would like advice on how to filter the table.

Can you please help me?

Thank you again!

[tamerj1 · Accepted solution]

Hi @MisterWolf 
Please refer to attched sample file with the solution.
I would create a seperate filter table (normally using power query) however, can also be created using DAX. This way I wouldn't have to double or tripple the number of rows of my staff table.

This the fllowing measure can be placed in the filter pane of the table visual, select "is not blank" and apply the filter.

[tamerj1 · Accepted solution]

Hi @MisterWolf 
Please refer to attched sample file with the solution.
I would create a seperate filter table (normally using power query) however, can also be created using DAX. This way I wouldn't have to double or tripple the number of rows of my staff table.

This the fllowing measure can be placed in the filter pane of the table visual, select "is not blank" and apply the filter.

[MisterWolf]

I'm relatively new at this and am having difficulty.

I'm not sure where to select "is not blank" - when I try to create the Teams Options List table like you did above, I get a "the arguments in GenerateSeries function cannot be blank" error.

Thank you for your patience. Hopefully I can learn this and then apply it moving forward.

[tamerj1 · Accepted solution]

@MisterWolf 

Use 

VAR Length = COALESCE ( PATHLENGTH ( Items ), 1 )

[MisterWolf]

That seems to work! When I run the slicer, I appear to get the correct count and records! Thank you!

I will go over everything you did and research it so I can understand exactly how it works!

Thanks again!

[MisterWolf]

Thank you! I was considering a separate filter table and I am glad you mentioned it.

It probably won't be for a day or two, but I appreciate your prompt response and will let you know what happens!

Thanks again!