How to Slice on mulitple columns not values within a column

[HelpAccount]

I'm trying to create a slicer where you select different columns and those columns appear inside a slicer and you can select which columns are shown on the report. Currnently I can only find a way of selecting one column to go inside a slicer and then the data within that column appears as the different options.

[v-huizhn-msft · Accepted solution]

Hi @HelpAccount,

I try to reproduce your scenario as follows.

I create the sample data table named Test.



Slicer is used to filter rows. So I right click the table->Edit Query, select all the columns->click Unpivot columns under Transfer on home page. Select Close&Apply, you will get the following table.



Create a slicer including Attribute column, and a table visual including value column. When you select the A, it will return all values under A field. When you select B, it will changes automatically.

 

If you have other issues, please feel free to ask.

Best Regards,
Angelia

[HelpAccount]

Thanks that works great!

One more questions. Imagine in your example I have letters that go all the way to Z so the list is very large. Is there a way to add hierarchies so I can group some of the letters together. Or alternatively add additional columns at the top which are a sum of multiple letters?

Example: A-E becomes one option, F-P another, Q-Z another. Then either you have the individual letters below this or even better you can expand the above 3 options?

[v-huizhn-msft]

Hi @HelpAccount，

Please add a calculated column to calculate groups, please reference to this similar case.

Then select the new calculated column and value->unpivot, you will get expected result.

Best Regards,
Angelia

[HelpAccount]

I used SUM to combine multiple columns. Is the correct calculated column? It appears in the report view but not the data view. I cannot find an option for value/ unpivot.