topic Re: Creating one slicer as a search tool for data derrived from multiple columns in DAX Commands and Tips

Creating one slicer as a search tool for data derrived from multiple columns

Anonymous — Wed, 17 Feb 2021 13:33:42 GMT

Hi Everybody,

I am working on a function in which team-managers should be able to search people based on their position and certain skills they possess in order to match people to the open position for which they search new people.

I get my data from an Excel sheet and it looks like this:

Employee	Position	Skill 1	Skill 2	Skill 3
1	Asset manager	Netwerken	aansturen	Motiveren
2	Asset manager	Anticiperen	Samenbindend leiderschap	Verbindend Leiderschap
3	Policy director	Analyserend vermogen	Integriteit	Ontwikkelen medewerkers
4	Policy director	Motiveren	Netwerken	aansturen
5	Asset manager	Motiveren	Netwerken	Integriteit

I want to create 1 slicer that helps to find the combination of skills that the team-manager wants. So, if I select 'Netwerken', 'Aansturen' & 'Motiveren', I only want to see this combination (underscored for employee 1 and 4) of skills. also it shouldn't matter in what order the skills are handed to me, as examplified in the table above.

Currently, if I make this selection, I get results for all people that possess one of these skills, but not the selectoin of these skills combined, which causes me to find a lot of people that don't have the combination of the skillls I am searching for.

I tried various things with DISTINCT and UNION formulas, but so far I wasn't able to get the correct result.

Do you have some advice for me?

Thanks!

Re: Creating one slicer as a search tool for data derrived from multiple columns

amitchandak — Wed, 17 Feb 2021 14:52:01 GMT

Anonymous , One way is unpivoting the table in power bi and use.

https://radacad.com/pivot-and-unpivot-with-power-bi

Another is to create a skill table, join with all three columns. Create three measures, 2 with use relationship to activate inactive joins.

Then add these measures to get final outcome

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

Re: Creating one slicer as a search tool for data derrived from multiple columns

Anonymous — Wed, 17 Feb 2021 15:53:07 GMT

I already tried the unpivoting, but this doesn't create the function that only the combination of selected values pops up. I get the whole list of people that just have one of the selected skills, while I only want to see the people that posess ALL of the skills that i select in the slicer.

Re: Creating one slicer as a search tool for data derrived from multiple columns

Anonymous — Thu, 18 Feb 2021 11:09:35 GMT

I tried your second option too, but also this doesn't provide me with my desired outcome. i tried various things and the same 2 things keep happening:

1. i make a selection of multple Skills it shows me all people that possess one of these skills, rather than the combination of these skills for one person

2. The whole page turns blank.

Could there be another way to fix this?