Trying to Create A Slicer

[Chaucer]

I've got a product table like so:

Product	Characteristics
1	A, B
2	B, C
3	C, D

I want to create a Slicer that has the Characteristics listed in it: A or B or C or D, and when that characteristoic is selected my table displays those products that inclue those characteristics. So, selecting B would list Products 1 & 2, selecting A would list Product 1.

I have attempted to do this by duplicating the table, naming the second table filter, then split column by delimiter, and then creating a relationship through the product on the tables.

However doing this only presents me with the option to slice by A, B or B, C or C, D.

I suspect I am massively overthinking this, but for the life of me I can't work out how to do what must be a very basic and common task? 

[Greg_Deckler · Accepted solution]

@Chaucer You could do this via a Complex Slicer by using searching (SEARCH) or CONTAINS and return 1 or 0 whether to display or not. 

https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534

Like:

Flag Measure =
  VAR __Slicer = SELECTEDVALUE('Slicer'[Column])
RETURN
  IF(SEARCH(__Slicer,'Table'[Column],,0)>0,1,0)

[PaulDBrown]

@Chaucer 

In Power Query:

1) split the 'Characteristics' column into two to get both values in separate columns, unpivot these two columns. You should get a column for 'Product' and another for the list of 'Characteristics'

2) reference this table to create the slicer using the single column 'Characteristics' with the A, B, C... values. Remove duplicates, load and create a One-to-Many relationship with your first table linking the 'Characteristics' columns

[Chaucer]

Guys, thank you so much for your suggestions. I don't know if I'm just having a bad week, but my little brain can't make either of your suggestions work.

@PaulDBrown I've knocked up some PB prefixed tables trying your method (Which looks super simple)

@Greg_Deckler I've got some GD prefixed tables trying yours (Which look super scalable)

Apologies for being so dim.

Gaaaaah.pbix 

[PaulDBrown · Accepted solution]

@Chaucer 

Ok, so here are the steps:

1) Original table:

 2) Select the Characteristics column and use the "Split Column" function in the ribbon, Choose "comma" as the delimiter:

3) Select the Product column, choose the Unpivot function in the ribbon and select "Unpivot other columns"

4) Remove the "Attribute" Column:

5) Rename the remaining columns. You might also want to clear up the new Characteristics column just in case: select it, go to the Transform tab , select Format in the ribbon and select "Trim" and then "Clean"

 6) Create a new table by referencing the Products table (I've called this new table "Slicer Characteristics"):

 7) Remove the "Products column" from this new table (Slicer Characteristics)

'8) Remove Duplicates from the remaining "Chracteristics" column:

9) load into the model, and create a one-to-many relationship between the "Slicer Characteristics" table and the Products Table:

10) Create a measure to list the selected characteristics to use in your table visual:

Selected Characteristics = 
VAR Charact = CONCATENATEX(VALUES('PB Products'[Characteristics]), 
'PB Products'[Characteristics], ", ")
RETURN
IF(ISINSCOPE('PB Products'[Product]), Charact)

And this is what you get:

If you want to include the other characteristics associated to the filtered products, you can use this measure:

Other associated Characteristics = 
VAR full = CALCULATETABLE(VALUES('PB Products'[Characteristics]), ALLEXCEPT('PB Products','PB Products'[Product]))
VAR Selected = VALUES('PB Products'[Characteristics])
VAR List = CONCATENATEX(EXCEPT(full, Selected),  'PB Products'[Characteristics], ", ")
RETURN
IF(ISBLANK([Selected Characteristics]), BLANK(), List)

Which gets you this:

If you wish to include the full list of Characteristics for the filtered products (instead of separate columns), you can use:

Full Characteristics (filtered) = 
VAR full = CALCULATETABLE(VALUES('PB Products'[Characteristics]), ALLEXCEPT('PB Products','PB Products'[Product]))
VAR List = CONCATENATEX(full,  'PB Products'[Characteristics], ", ")
RETURN
IF(ISBLANK([Selected Characteristics]), BLANK(), List)

I've included the PBIX file for your reference.

[Chaucer]

Crikey @PaulDBrown 
That is the most hardcore reply I've ever had. Thanks!

[PaulDBrown]

@Chaucer 

Yea, sorry about that...I may have gone a bit overboard in the detail...Apologies if stuff was a bit obvious...

[Chaucer]

Haha, no it was awesome. I've learnt a bunch of new tricks just working through your steps that I wouldn't have otherwise! Thanks!

[Greg_Deckler · Accepted solution]

@Chaucer You could do this via a Complex Slicer by using searching (SEARCH) or CONTAINS and return 1 or 0 whether to display or not. 

https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534

Like:

Flag Measure =
  VAR __Slicer = SELECTEDVALUE('Slicer'[Column])
RETURN
  IF(SEARCH(__Slicer,'Table'[Column],,0)>0,1,0)

[Chaucer]

Hey @Greg_Deckler 
I'm getting an error using SEARCH in a measure. 

"A single value for column 'Characteristics' in table 'GD Products' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

Am I being silly?

Don't panic. I resolved it by using contains instead.

Thanks!

Thanks

[amitchandak]

@Chaucer , would it possible for you to split this column by row

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

[Chaucer]

Hi @amitchandak ,
That's exactly what I did to create the problem!