cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## 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 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. 🙂

1 ACCEPTED SOLUTION
Solution Sage

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:

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

3 REPLIES 3
Super User

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Solution Sage

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:

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

Frequent Visitor

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

thank you.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors