Single slicer for multiple values in row

[Anonymous]

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!

[Anonymous]

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

[Samarth_18 · Accepted solution]

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

[amitchandak]

@Anonymous , My suggestion would be to split the row based on ;# in power query

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

[Anonymous]

@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!