Forum Discussion

Chaucer's avatar
Chaucer
Helper II
5 years ago
Solved

Trying 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 ...
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    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.