cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Creating slicers for values in order to choose which value we want for each column

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:

PersonSkill 1Skill 2Skill 3Skill 4Skill 5Skill 6
Person 1BeginnerAdvancedBeginnerIntermediateBeginnerBeginner
Person 2AdvancedAdvancedIntermediateIntermediateAdvancedBeginner
Person 3BeginnerAdvancedAdvancedIntermediateAdvancedBeginner
Person 4BeginnerIntermediateIntermediateBeginnerAdvancedAdvanced
Person 5IntermediateBeginnerBeginnerBeginnerBeginnerAdvanced

 

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!

4 REPLIES 4
Post Prodigy
Post Prodigy

As @pranit828 suggests you have to unpivot your data. Power BI works with tables that are easy to filter, which means they should be long but narrow as opposed to short and wide.
Super User II
Super User II

HI @Twelve 

I would unpivot all 6 Skill columns,

pranit828_3-1598748124915.png

Then My table would look like as below

pranit828_2-1598748044418.png

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.

pranit828_1-1598747984503.png

And now when you project your person's name in the table, it will display only those who have the selection.

pranit828_4-1598748241997.png

Also, you can have level on different slicers as you need.





PBI_SuperUser_Rank@1x.png
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 🙂

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors