Forum Discussion
Filtering by column headings
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.
It is actually possible to do this, but I don't really consider it a "clean" way.
- edit the query
- duplicate the table you want to filter based on columns
- select all the columns you want to be able to filter (using shift and or control)
- right-click and select unpivot data
- 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
10 Replies
It is actually possible to do this, but I don't really consider it a "clean" way.
- edit the query
- duplicate the table you want to filter based on columns
- select all the columns you want to be able to filter (using shift and or control)
- right-click and select unpivot data
- 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
Thanks for that @TimGab.
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.
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 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.
Can you post some image samples of what you have and what you want.
Thanks Matt, the data is very sensitive so I can't send it on, unfortunately.
Thanks for the help though.
It is very common to have sensitive data - normally 99% of the time. Most people just create sample data to explain the problem.
