Forum Discussion
Trying to Create A Slicer
- 5 years ago
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) - 5 years ago
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.
Yea, sorry about that...I may have gone a bit overboard in the detail...Apologies if stuff was a bit obvious...
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!