cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Filter for a series of dummy variables

Hello!

I´m trying to create a new slicer for a series of dummy variables. My data is something like this:

Capture.PNG
My idea is to have one slicer that allow users to select the equipments that they want, then the dashboard should show all projects that have "1" in the equipments selected. Is it possible to do so or do I need to create one slicer for each Equipment and indicate to select "1" option in each slicer?

Thanks in advance!

Pedro

1 ACCEPTED SOLUTION
v-diye-msft
Community Support
Community Support

Hi @Anonymous 

 

1. Created a table as your requested.

002.PNG

2.  Unpivot the table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLjFU0lEyAGJDKDZQitWBSxqhSIBoJEljFAk0nSZQCYTRsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ProjectID = _t, Equipment1 = _t, Equipment2 = _t, Equipment3 = _t, Equipment4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectID", type text}, {"Equipment1", Int64.Type}, {"Equipment2", Int64.Type}, {"Equipment3", Int64.Type}, {"Equipment4", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ProjectID"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

 003.PNG

3.  Filter the value is 1 and set the attributes as slicer:

004.PNG

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

2 REPLIES 2
v-diye-msft
Community Support
Community Support

Hi @Anonymous 

 

1. Created a table as your requested.

002.PNG

2.  Unpivot the table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLjFU0lEyAGJDKDZQitWBSxqhSIBoJEljFAk0nSZQCYTRsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ProjectID = _t, Equipment1 = _t, Equipment2 = _t, Equipment3 = _t, Equipment4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectID", type text}, {"Equipment1", Int64.Type}, {"Equipment2", Int64.Type}, {"Equipment3", Int64.Type}, {"Equipment4", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ProjectID"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

 003.PNG

3.  Filter the value is 1 and set the attributes as slicer:

004.PNG

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

d_gosbell
Super User
Super User

You can't really do what you want with the data structured the way it is. I would suggest using an unpivot operation in your query to convert the equipment columns into rows, then filter to only include the rows with 1's.

 

So the end result would be:

 

Project 1    Equipment 2     1

Project 1    Equipment 3     1

Project 2    Equipment 1     1

Project 2    Equipment 2     1

...

 

Then you can have a single slicer on the Equipment column.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.