Forum Discussion
Helper IITrying to Create A Slicer
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?
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)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.
10 Replies
- Greg_Deckler
Community Champion
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)
ChaucerHey 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
PaulDBrownIn 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.
- PaulDBrown
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.
- amitchandak
Super User
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!
