Make a table in PowerBI based on multiple selections in another table

Question

I have the following table, correlating people with activities:

Name	Age	Cyclist	Canoeist	Hiker	Runner
Bill	45	X		X	X
Susie	28		X
Fred	33	X	X
Thys	28		X	X
Lin	67			X	X

I wish to have a table showing the names and ages, where the user can select one or multiple activities.

For instance, someone can select "Hiker" and the table will show Bill, Thys, and Lin. If they select "Hiker" and "Runner", they get Bill & Lin.

I am not sure how to do this. I am open to suggestions on how to structure the data table to make this possible. I can drag the individual activity columns in as filters for the visual, but I want something that's easier for end users.

Answer 1

When loading the data into PBI, I changed X to 1 and empty values to 0. Then based on the Cyclist, Canoeist, Hiker and Runner columns, I unpivoted the data in PQ :

Then keep only the rows where Participates = 1 :

The full PQ code :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsrMyVHSUTIxBRKGQGwApQ2VYnWilYJLizNTgTwjCyQpAzAGSbsVpaYAOcbGcE3IsiEZlcXoeg3hsj6ZeUC2mTlcC9zeWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Age = _t, Cyclist = _t, Canoeist = _t, Hiker = _t, Runner = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Age", Int64.Type}, {"Cyclist", Int64.Type}, {"Canoeist", Int64.Type}, {"Hiker", Int64.Type}, {"Runner", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name", "Age"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Activity"}, {"Value", "Particpates"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Particpates] = 1))
in
    #"Filtered Rows"

Then use Activity in a slicer :