Filtering by column headings

[douglasmarc]

Hi

I have a range of columns with relevant data and would like to enable users of the reports to filter (using slicer or otherwise) by the column headers. They are participation figures so the data is split by region and year and each parameter is in a separate column.

Failing this if there is a way to reorder the information easily in PBI to have the data listed by region year and parameter (going from wide short table to narrow long table) that would be a possible solution either.

Thanks in advance.

[TimGab · Accepted solution]

It is actually possible to do this, but I don't really consider it a "clean" way.

1. edit the query
2. duplicate the table you want to filter based on columns
3. select all the columns you want to be able to filter (using shift and or control)
4. right-click and select unpivot data
5. now you can filter based on the new "attributes" column

Notice how this is not a good suggestion for large data as you have to copy the entire table.

It works though...

To actually implement the filter I would recommend using the chiclet slicer from the custom visuals store 

[Erik1]

I have the same issue. Time series data can't be filtered like such. I have large number of columns/indices and a date variable. I would've liked to see simple/yet very practical features like column filters added to the software.

[Chenp]

It can be done.

You just need to go to "Modeling" -> New parameter -> Fields -> Drag the column (fields) that you want to include in the slicer into "Add and reorder fields" (Leave the "Add slicer to this page" option checked) -> Create.

Once you click Create, you will see the measure you just created in the right-hand data panel. Drag the measure to the visual values. The slicer you just created will now filter the visual columns based on the selected column name.

[arcatra]

Hey thank you very much for the solution but actually my new table with the unpivoted attributes has no relations with the other tables so the slicer of the attributes doesn't work. How can i do?

I tried to add also the column ID and i linked it to the other table but still nothing.

[arcatra]

Hey thank you very much for the solution but actually my new table with the unpivoted attributes has no relations with the other tables so the slicer of the attributes doesn't work. How can i do?

I tried to add also the column ID and i linked it to the other table but still nothing.

[douglasmarc]

Thanks for that @TimGab.

[MattAllington]

Can you post some image samples of what you have and what you want. 

[douglasmarc]

Thanks Matt, the data is very sensitive so I can't send it on, unfortunately.

Thanks for the help though.

[MattAllington]

It is very common to have sensitive data - normally 99% of the time. Most people just create sample data to explain the problem. 

[ankitpatira]

@douglasmarc showing or hiding column based on expression is not currently supported and you should vote for this idea here. however you can drag and resize columns to hide another columns. In table visual hover over space between two columns until you get <--> sign then simply drag it cross the right as much as you to make columns wider or shorter. but again that is manual solution and can't be done using expression or some logic.