Forum Discussion
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. 🙂
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'!
6 Replies
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'!
would you mind giving me the working file for easy reference please?
thank you.
This does not work when there are duplicate values...
To get around the duplicate problem you just need to include the primary key from the original table in the COMBINEVALUES function.
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"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.
