Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
tomcch
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. 🙂

tomcch_0-1694185654785.png

 

 

 

1 ACCEPTED SOLUTION
CoreyP
Solution Sage
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:

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. 
CoreyP_0-1694388549353.png


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

CoreyP_1-1694388620871.pngCoreyP_2-1694388649007.png

And you are rockin' and rollin'!

View solution in original post

3 REPLIES 3
parry2k
Super User
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.

CoreyP
Solution Sage
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:

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. 
CoreyP_0-1694388549353.png


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

CoreyP_1-1694388620871.pngCoreyP_2-1694388649007.png

And you are rockin' and rollin'!

tomcch
Frequent Visitor

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

thank you.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.