Filter a table by data that has multiple options

[Kerslaing]

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

[MFelix · Accepted solution]

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.

[Kerslaing]

That works great, thanks.

[MFelix]

Don't forget to mark the correct answer to help others.