Forum Discussion
Single Slicer to Filter Multiple Columns
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 , I faced the same issue but I made it work. I have big data table, let's call it the same as this trend's example table - Shipments, where same A, B ..s can appear in the same order multiple times. In this case, the keys are not going to be unique in Slicer table, and so when Slicer is put in relationship with Shipments table via Key, PBI gives a warning that many:many join is ocurring. It is OK if you use the Slicer as filter only in your views. You have to make sure the flow of filter is directed from Slicer to Shipments, though.