Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone,
I tried to look for a similar question but it's been quite difficult since I'm having trouble phrasing what I want (as the title shows).
I have a table that looks like this:
Person | Skill 1 | Skill 2 | Skill 3 | Skill 4 | Skill 5 | Skill 6 |
Person 1 | Beginner | Advanced | Beginner | Intermediate | Beginner | Beginner |
Person 2 | Advanced | Advanced | Intermediate | Intermediate | Advanced | Beginner |
Person 3 | Beginner | Advanced | Advanced | Intermediate | Advanced | Beginner |
Person 4 | Beginner | Intermediate | Intermediate | Beginner | Advanced | Advanced |
Person 5 | Intermediate | Beginner | Beginner | Beginner | Beginner | Advanced |
I'd like to create 3 slicers (one for each skill level) that would have all the columns "Skill" as possible values. That way, I could filter on the rows based on the skill level of the chosen columns.
Basically, I would have:
- Slicer 1: Beginner
- Slicer 2: Intermediate
- Slicer 3: Advanced
Each slicer would have "Skill 1", "Skill 2", "Skill 3", "Skill 4", and "Skill 5" as possible values.
That way, if I select "Skill 1" on slicer Beginner, I would get the rows for Person 1, 3, and 4.
If I select "Skill 1" on slicer Beginner and "Skill 3" on slicer Advanced, I would get the row for Person 3.
If I select "Skill 2" and "Skill 3" on Intermediate and "Skill 5" on Advanced, I would get Person 4, an so on.
Thank you for your help!
HI @Anonymous
I would unpivot all 6 Skill columns,
Then My table would look like as below
Here is the M query if it helps:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PUzBU0lFySk3PzMtLLQIyHVPKEvOSU1NQRT3zSlKLclNTMhNLUlFl4MxYHbiRRqjmIDHRzEHjYrMbyVhjnC7FbQMBI03wehO3r7HZjWSsKXFBhouJMDIWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, #"Skill 1" = _t, #"Skill 2" = _t, #"Skill 3" = _t, #"Skill 4" = _t, #"Skill 5" = _t, #"Skill 6" = _t]),
#"Unpivoted Only Selected Columns" = Table.Unpivot(Source, {"Skill 1", "Skill 2", "Skill 3", "Skill 4", "Skill 5", "Skill 6"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Only Selected Columns",{{"Attribute", "Skill"}, {"Value", "Level"}})
in
#"Renamed Columns"
Then I would create a slicer like.
And now when you project your person's name in the table, it will display only those who have the selection.
Also, you can have level on different slicers as you need.
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
Oh, it seems like I can only select one value at a time.
Is there a way to allow for multiple selection, such as "Skill 1" in the "Beginner" category and "Skill 2" in the "Advanced" one?
Thank you!
Thank you @pranit828, it works perfectly!
I am still new to power BI and I never realized that it was possible to use two columns in a slicer.
Thank you @daxer-almighty for your input, I'll keep that in mind for when I'm stuck 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |