Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Once Slicer for multiple columns

Hi, I am trying to create a single slicer for the below table, i would like to see all of the table when i first load the report, but when i come to slicer "Part One/Part Two/Part Three/Part Four/Par...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi Anonymous,

     

    There is a solution below. Please check out the demo in the attachment.

    1. Create a new table as slicer table.

    SlicerTable =
DISTINCT (
    UNION (
        VALUES ( Table1[Part One] ),
        VALUES ( Table1[Part Two] ),
        VALUES ( Table1[Part Three] ),
        VALUES ( Table1[Part Four] ),
        VALUES ( Table1[Part Five] )
    )
)

    2. Rename the column name of the new table. (optional)

    3. Do not establish any relationships!

    4. Create a measure.

    Measure =
IF (
    MIN ( 'Table1'[Part One] ) IN VALUES ( SlicerTable[values] )
        || MIN ( 'Table1'[Part Two] ) IN VALUES ( SlicerTable[values] )
        || MIN ( 'Table1'[Part Three] ) IN VALUES ( SlicerTable[values] )
        || MIN ( 'Table1'[Part Four] ) IN VALUES ( SlicerTable[values] )
        || MIN ( 'Table1'[Part Five] ) IN VALUES ( SlicerTable[values] ),
    1,
    BLANK ()
)

    Once_Slicer_for_multiple_columns

     

    Best Regards,

    Dale

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Even if it is not a slicer, can this be done in a table as show with filters?

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi Anonymous,

 

There is a solution below. Please check out the demo in the attachment.

1. Create a new table as slicer table.

SlicerTable =
DISTINCT (
    UNION (
        VALUES ( Table1[Part One] ),
        VALUES ( Table1[Part Two] ),
        VALUES ( Table1[Part Three] ),
        VALUES ( Table1[Part Four] ),
        VALUES ( Table1[Part Five] )
    )
)

2. Rename the column name of the new table. (optional)

3. Do not establish any relationships!

4. Create a measure.

Measure =
IF (
    MIN ( 'Table1'[Part One] ) IN VALUES ( SlicerTable[values] )
        || MIN ( 'Table1'[Part Two] ) IN VALUES ( SlicerTable[values] )
        || MIN ( 'Table1'[Part Three] ) IN VALUES ( SlicerTable[values] )
        || MIN ( 'Table1'[Part Four] ) IN VALUES ( SlicerTable[values] )
        || MIN ( 'Table1'[Part Five] ) IN VALUES ( SlicerTable[values] ),
    1,
    BLANK ()
)

Once_Slicer_for_multiple_columns

 

Best Regards,

Dale

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    This solution is quite elegant and is exactly what I am looking for.

    Now a twist. What if the data is coded and converted via links and relationships? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Is this only available for tables? What if I wanted to use this for a stacked column chart?

  • selpaqm's avatar
    selpaqm
    Helper V
    6 years ago
    Hi v-jiascu-msft , your solution is worked for extended version 6 different column. unless, it takes ages when i click the slicer. is there any faster way? Regards,
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Really helpful solution, thanks!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hello, thank you so much for your solution.

     

    I have done it step by step and I have downloaded your powerbi file and did the same. However, its not filtering based on the "values" slicer. Any idea why or how I can solve it?

     

    Your help is very much appreicated.

     

    Thanks,

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      In case if some one faces the same issue. Add the Measure to the table viz. 

  • sanjanarama's avatar
    sanjanarama
    Resolver I
    4 years ago

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    I tried same solution for matrix and it is not working, can anyone help out

     

     

  • akhilduvvuru's avatar
    akhilduvvuru
    Helper IV
    3 years ago

    v-jiascu-msft - This slicer is impacting the other slicers. If I apply any other filter from filter pane, the table is not filtering. Can you help me with the same please? Thanks!

  • pbijess857's avatar
    pbijess857
    Frequent Visitor
    3 years ago

    This is so helpful. I hope you have a great day - you deserve it. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    HI This is working for 1 slicer what I want to design 3 slicers to look in 6 columns. 

    My data looks like Name A, Name B, City A , City B , State A , State B . I tried the above solution and Name in ( Name A , Name B ) slicer is working, But if I configure the same along with (City A, City B ), (State A, State B) its not working for me. Please advise.