Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Single slicer for multiple values in row

Hi all,

I have this Data set:

 

 

I would like to create a slicer that relay on col 2 (i.e. "Beer type") -  include "split" this col to unique parametes like that:

 

Therfore I tried to copy this table and create split option via Text.split in the M query - however I didn't recvied any desirable results becuase it's not fit to the details from my original table.

Actually I would like to create slicer as above but I want to filter it with the original values i.e. if I click on "A" I would like to see the results as below (highlighted in yellow):

 

 

 

This is possible?

I really appriciate your help with it.

Thanks a lot!



Need Help
Tips and Tricks
  • Samarth_18's avatar
    Samarth_18
    5 years ago

    Hi Anonymous ,

     

    You can achive it by following below approach:-

    1. Duplicate your table

    2. Remove other two columns from duplicate table

    3. Now split column by delimeter and in advance option select split into rows

     

    4. You will see below result

    5. Now remove duplicate and clikc on close & apply

    6. Now add types from new duplicate table into slicer and other details from your table in table visual.

    7. Create a measure for filtering your table based on selection from slicer with below code and used it as visual level filter

    _Filter = 
IF (
    CONTAINSSTRING (
        MAX ( 'Beer Data'[Type] ),
        SELECTEDVALUE ( 'Beer Data_duplicate'[Type] )
    ),
    1,
    0
)

     

     Once you follow above steps it will work as expected:-

     

     

    Thanks,

    Samarth

     

     

     

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandak 
      I did it but it's not working becuse the split option is not includt the next parameters overthere.

      I create two table - one is the Original table with the Original details as above

      The second table is with the split option:


      I want to see my original table in the dashboard. therfore I create a relationship between them.

      However when I click on some letter I don't get what I want to see:

       

      Thanks in advanced!

       

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Anonymous ,

     

    You can achive it by following below approach:-

    1. Duplicate your table

    2. Remove other two columns from duplicate table

    3. Now split column by delimeter and in advance option select split into rows

     

    4. You will see below result

    5. Now remove duplicate and clikc on close & apply

    6. Now add types from new duplicate table into slicer and other details from your table in table visual.

    7. Create a measure for filtering your table based on selection from slicer with below code and used it as visual level filter

    _Filter = 
IF (
    CONTAINSSTRING (
        MAX ( 'Beer Data'[Type] ),
        SELECTEDVALUE ( 'Beer Data_duplicate'[Type] )
    ),
    1,
    0
)

     

     Once you follow above steps it will work as expected:-

     

     

    Thanks,

    Samarth

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

    I created a sample pbix file(see attachment), please check whether that is what you want.

    Power Query Editor:

    1. Duplicate the column "Type" name as "nType"

    2. Split the new column "nType" into rows by delimiter ";#"

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RYs7CsAgEAWvErQV3u6aYIKVn5xCLL1A7l9ETcDi8WCGKUXF1p4tKKOC17GfkBBYwKqa3w6c+vi6LHYcywyaZ0QCh3OZQaPX+cscmGBp6XuEXqfZsoVAelxf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Beer name" = _t, Type = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Beer name", type text}, {"Type", type text}, {"Date", type date}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Type", "nType"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Duplicated Column", {{"nType", Splitter.SplitTextByDelimiter(";#", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "nType"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"nType", type text}})
in
    #"Changed Type1"

     

    3.  Create a slicer with new column "nType"Best Regards