Single Slicer to Filter Multiple Columns

[tomcch]

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 shipment 1/2/3/4/5

if filter = "B", then result should be shipment 2/3/6

I tried to follow some of the tutorial here, which is about to create a new table and new measure...but doesn't work, i cannot select the new measure as a slicer.
Appreciate for your assistance...thank you. 🙂

[parry2k]

@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"

[CoreyP · Accepted solution]

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'!

[joshbonner1986]

This does not work when there are duplicate values...

[tomcch]

would you mind giving me the working file for easy reference please?

thank you.