powerbi

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


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.


Solution

  • 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 :

    enter image description here

    enter image description here

    Then keep only the rows where Participates = 1 :

    enter image description here

    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 :

    enter image description here

    enter image description here

    enter image description here

    enter image description here