Forum Discussion

Sumit626's avatar
Sumit626
Frequent Visitor
3 years ago
Solved

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

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 

 



Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Sumit626
      Frequent Visitor
      3 years ago

      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's avatar
        Anonymous
        Not applicable
        3 years ago

        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.