Unique list values in filter and when multiple values selected should filter comma seperated entry

[Sumit626]

i have a list of tags some of which are comma-separated as shown in the image below

when I use the same column in the filter it shows comma-separated entries as well as shown below

 

i want a list of unique entries in filter and when some select 2 tags such as operation and gxp it should show only those rows where tags are operations, gxp 

and not where there are individual entries of operations and gxp

 

Required tags filter

 

any help would be really appreciated thanks 

 

[Anonymous]

Hi @Sumit626 

You can refer to the following example.

Put the following code in Advanced Editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKi4pSixJTc9M1kmvKFDSUTI0UIrViVYCcnTyC1KBcpn5ecVAcSOIOFR5KVi5TllqalkiUNIYKBkLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Tags = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Tags", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Tags", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Tags"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Tags", type text}})
in
    #"Changed Type1"

Then put the new "Tags" columns to the slicer.

The information above just use the "split column by delimiter " function.

You can refer to the following link to know more about it.

Split columns by delimiter - Power Query | Microsoft Learn

Best Regards!

Yolo Zhu

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

[Sumit626]

hi 
i've created a unique list of filters using the following dax

Table 2 =

VAR _path =

    SUBSTITUTE (

        CONCATENATEX ( VALUES ( Sheet1[Tags] ), Sheet1[Tags], "," ),

        ",",

        "|"

    )

RETURN

    DISTINCT (

        SELECTCOLUMNS (

            ADDCOLUMNS (

                GENERATESERIES ( 1, PATHLENGTH ( _path ), 1 ),

                "Desc", PATHITEM ( _path, [Value] )

            ),

            "Desc",IF([Desc]<>"", [Desc]," ")

        )

    )


the issue is when i select for example gxp and operations it display all the entries containing either gxp or operations while the required behaviour is it should online display rows with gxp,operations

[Anonymous · Accepted solution]

Hi @Sumit626 

You can create a new measure

Measure = var a=CONCATENATEX(VALUES('Table 2'[Desc]),[Desc],",")
return IF(ISFILTERED('Table 2'[Desc]),CALCULATE(SUM(Sheet1[value]),FILTER(Sheet1,MAX(Sheet1[Tags])=a)),SUM(Sheet1[value]))

Note:Don't create the relationship between two tables

Output

Best Regards!

Yolo Zhu

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

[AGhosh]

Hi,

I am a beginner at Power BI, and I have the same query as the one posted here. While your solution works for Table visual, It doesn't seem to work for card/ donut chart visual.

How can that be solved?

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.