Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MisterWolf
Frequent Visitor

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

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

IDNameTeam Options
12314Ed MercerDeals Direct With Friend
12345Kelly GraysonDeals Direact With Friend; Deals Direct With Third Party
23453BortusDeals Direct With A Pal; Deals Direct With Third Party
234324Claire FinnTends to Others
234234John LamarrDeals Direct With Friend; Tends to Others
324324Gordon MalloyDeals Direct With Friend; Deals Direct With a Pal, Deals Direct With Third Party; Tends to Others
34323Alara KitanDeals Direct With Third Party; Tends to Others
2433214Talla Keyali 
343245IsaacDeals 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!

 

 

2 ACCEPTED SOLUTIONS
tamerj1
Super User
Super User

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.

1.png2.png3.png4.png

 

View solution in original post

@MisterWolf 

Use 

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

View solution in original post

5 REPLIES 5
tamerj1
Super User
Super User

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.

1.png2.png3.png4.png

 

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.

@MisterWolf 

Use 

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

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!

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors