Forum Discussion

tomcch's avatar
tomcch
Frequent Visitor
2 years ago
Solved

Single Slicer to Filter Multiple Columns

Hi, i am a beginner of Power BI. I have data like below. I would like to create a slicer to allow user to filter shipment that involve a particular party. If filter = "A", then result should be sh...
Need Help
Tutorial Requests
  • CoreyP's avatar
    CoreyP
    2 years ago

    Hi Tomcch,

    I was actually curious about this so I decided to have a go at it. I found a video online and worked through it and got it to work! Here's what you need to do:

    1.  Add a calculated column to your shipments table. 

    Key = COMBINEVALUES( "|" , Shipments[Agent] , Shipments[Consignee] , Shipments[End Customer] , Shipments[Payee] , Shipments[Shipper] )

    2.  Create a new calculated table.
    Slicer =
    DISTINCT(
    UNION(
        SELECTCOLUMNS( Shipments , "Selection" , Shipments[Agent] , "Key" , Shipments[Key] ) ,
        SELECTCOLUMNS( Shipments , "Selection" , Shipments[Consignee] , "Key" , Shipments[Key]  ) ,
        SELECTCOLUMNS( Shipments , "Selection" , Shipments[End Customer] , "Key" , Shipments[Key]  ) ,
        SELECTCOLUMNS( Shipments , "Selection" , Shipments[Payee] , "Key" , Shipments[Key]  ) ,
        SELECTCOLUMNS( Shipments , "Selection" , Shipments[Shipper] , "Key" , Shipments[Key]  )
    ))

    3. Establish a relationship between the tables on Key, set to bi-directional filtering. 


    4.  On your report canvas, add a slicer using the "Selection" field. 

    And you are rockin' and rollin'!

parry2k's avatar
parry2k
Super User
2 years ago

tomcch From a scalability and best practice perspective, unpivot your data. Start a blank query, click Advanced Editor, and paste the following M code. Close and apply.

 

Use the Value column in the slicer and shipment in a table visual, As you select different value in the slicer, you will see the respective shipments.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYlcgdgNiZyCOUIrViVYyArKckGRdgDgSLGMMVYcsGwWWMYHynKHqIbIgGVOoCMwON7AsSMYMynOBmugOVhEbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Shipment = _t, Shipper = _t, Consignee = _t, #"End Customer" = _t, Agent = _t, Payee = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Shipment", Int64.Type}, {"Shipper", type text}, {"Consignee", type text}, {"End Customer", type text}, {"Agent", type text}, {"Payee", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Shipment"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"