Forum Discussion
Filter a table by data that has multiple options
Hi, I am creating a small software catalogue of software used across the organisation and by which departments
data is all in one table
softwarename dep1 dep2 dep3
| testsoftware1 | YES | NO | NO |
| testsoftware2 | NO | YES | YES |
| testsoftware3 | YES | NO | YES |
| testsoftware4 | YES | NO | NO |
| testsoftware5 | NO | NO | YES |
I would like to create a slicer that allows the selection of a department and in a table visual shows all the software that the selected department has access to.
I had though of creating a power bi table with the departments for the slicer but then how do i get it to filter the software table when all it has is yes's and no's.
thanks for the help
Hello @Kerslaing ,
The best option is in the query editor to despivot your departments and get a table with 3 columns:
- SoftwareName
- Attribute (Department Names)
- Value (yes/no)
You can then rename the columns and use the attribute column in the slicer to check the full code for the power query and the PBIX file below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLinOTyspTyxKNVTSUYp0DQaSfv4QIlYHVYURTA6iDESiKzFGNQSbEhOC9pgiyUHNiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [softwarename = _t, dep1 = _t, dep2 = _t, dep3 = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"softwarename"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Department"}, {"Value", "HasSoftware"}}) in #"Renamed Columns"Then filter the visualizations by the value column.
3 Replies
Hello @Kerslaing ,
The best option is in the query editor to despivot your departments and get a table with 3 columns:
- SoftwareName
- Attribute (Department Names)
- Value (yes/no)
You can then rename the columns and use the attribute column in the slicer to check the full code for the power query and the PBIX file below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLinOTyspTyxKNVTSUYp0DQaSfv4QIlYHVYURTA6iDESiKzFGNQSbEhOC9pgiyUHNiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [softwarename = _t, dep1 = _t, dep2 = _t, dep3 = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"softwarename"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Department"}, {"Value", "HasSoftware"}}) in #"Renamed Columns"Then filter the visualizations by the value column.
