Filter a merged column using just one of the items in that merged column

[dphillips]

Is it possible to use a slicer which has distinct values, to filter a column in a table which has been merged and using commas as a delimiter?

For example - here is a list of distinct codes.

ConstitCode

@STFP

@CRD

@PAY

STFP

@PAYP

@PF

@Anonymous

@ZDN

@SP

@PP

@STF

@SFOC

ONUHL

FS

70Club

MOONS

ONUL

@deb

CONTR

MUSTUT

moons

@PCOC

@PC

@EDS

@sf

PCM

PP

@PCEC

FOUNBDPAST

FRNC

@SCEC

VOL

ONUPP

DVPTV

THEOSTUDPST

NCMEDALINTL

NCMEDALAUS

ONUC

ONUEXEC

CM

DVPTS

FOUNBD

@DebS

@SFEC

@SCOC

FSAS

INTERN

@sc

WEEMP

@EdC

ServPro

SPEXT

I want to use these to filter a column I have merged in another table. eg if I select @CRD  in the slicer, it will return rows 5, 7, 21 and 33 in the table below. This is because @CRD is just one of the values in that row.

The table holding the distinct values is currently not related to the table with the merged columns.

IDAllCodes

1	@STFP
2	@STFP
3	@STFP
4	@STFP
5	@CRD,@STFP
6	@STFP
7	@CRD,@PAY,@STFP
8	@STFP
9	@STFP
10	@STFP,STFP
11	@STFP
12	@PAYP,@STFP
13	@PF,@STFP,STFP
14	@STFP
15	@STFP
16	@STFP
17	@STFP
18	@STFP
19	@STFP
20	@STFP
21	@CRD,@PAYP,@STFP,STFP
22	@STFP
23	@STFP
24	@STFP
25	@STFP,STFP
26	@PAYP,@STFP,STFP
27	@STFP
28	@PAYP,@STFP
29	@STFP
30	@PF,@STFP
31	@STFP
32	@STFP
33	@CRD,@DEBP,@PAYP,@STFP,@ZDN,STFP

Is there anyway of doing this? Any help would be appreciated.

[smpa01]

@dphillips  you can use a measure like this

Measure = 
VAR jn =
    COUNTROWS (
        FILTER (
            GENERATE (
                SUMMARIZE (
                    ADDCOLUMNS ( 'fact', "@path", SUBSTITUTE ( [Column2], ",", "|" ) ),
                    [@path]
                ),
                ADDCOLUMNS (
                    GENERATESERIES ( 1, PATHLENGTH ( [@path] ), 1 ),
                    "@items", PATHITEM ( [@path], [Value], TEXT )
                )
            ),
            ( [@items] ) IN ( SUMMARIZE ( slicer, slicer[Column1] ) )
        )
    )
VAR val =
    IF ( ISBLANK ( jn ) = FALSE (), MAX ( 'fact'[Column2] ) )
RETURN
    val

[dphillips]

Thanks @smpa01 . This works in filtering a table which is great. Not thinking, I should have mentioned that there are other columns in the table. There is a column called receiptAmounts which has dollar amounts that people have donated under their specific code. I would love to show receiptAmounts in a column graph but I am struggling to work out how to do this - you can't use the measure to filter the table of course. See below - I have the slicer on the left and it is filtering the table at the top - only 2 rows showing with a total amount of $250. How can I use this measure in a bar chart for example? The bar chart below has all the combinations of codes. When I select an item in the slicer how can I get this to filter the bar chart? Thanks again for your help.

[smpa01]

You need to drop a similar measure in the bar chart

Measure 2 = 
VAR jn =
    COUNTROWS (
        FILTER (
            GENERATE (
                SUMMARIZE (
                    ADDCOLUMNS ( 'fact', "@path", SUBSTITUTE ( [Column2], ",", "|" ) ),
                    [@path]
                ),
                ADDCOLUMNS (
                    GENERATESERIES ( 1, PATHLENGTH ( [@path] ), 1 ),
                    "@items", PATHITEM ( [@path], [Value], TEXT )
                )
            ),
            ( [@items] ) IN ( SUMMARIZE ( slicer, slicer[Column1] ) )
        )
    )
VAR val =
    IF ( ISBLANK ( jn ) = FALSE (), sum ( 'fact'[Column3] ) )
RETURN
    val

[Padycosmos]

Hope this helps:

[MURTAZA]

There are a couple of ways to filter a column based on a list. See the link below
https://bi.unija.com/en/dynamic-filtering-using-a-list-in-power-query/

If you want it to work with slicer, then Power BI has a new feature called Query Parameters, but this requires your data to be connected via DirectQuery
https://learn.microsoft.com/en-us/power-query/power-query-query-parameters

Hope this helps.