Dynamic filtering table with no physical relationship

[renaudstaessens]

Hi guys would really need your help cause i don't know how to solve the following problem : 

Today i have a data model with approximately 20 tables :

• My "fact" table is the Tasks table whichcontains basically all the tasks that Employees have to do ( a task is assigned to one employee) . The join is done using the email of the employee.
• All Employees have a certain set of "Skills" . Skills are for example (java,php,.Net ....)
• I have also a When Free Table which determines the start date for each employee when he becomes  free ( no more tasks to do based on start date and end date of tasks ).
• 

All this part is OK up to now and is working .

Now the new challenge is  :  I have a new Staffing Needs table which contains demand of people with certain skills (ex : we are looking for a guy that knows  Php AND java AND .net  and another demand with a guy that knows C++)

Today the Staffing Needs table looks like this

Demand Id	Skills	Rating(1= basic knowledge ,2 =expert)
1	php	1
1	java	1
1	.net	2
2	php	1
2	C++	2

what i d like to do is :

 1) having a visual listing all demands so for example a table like (it's straightforward it's just to explain the behavior 🙂

demand id	summary
1	one guy with php and java and.net skill
2	one guy with C++ skill

2) a date slicer to choose the starting date ( ie filter guys that will be available after the selected date based on the WhenFree table) 

3) The tricky part  : when i select a line in the first visual then  the  second visual table is filtered accordingly  . This second visual lists ONLY the name & email of available employees  (based on the date slicer) that have ALL the skills required for the demand selected ( in the case i select demand id = 1=> then i want to see ONLY people having ALL the 3 skills and not only one of the skill). 

I m really stucked any help would be deeply appreciated 🙂

regards

Renaud

[sturlaws · Accepted solution]

Hi @renaudstaessens 

have you managed to solve your issue?

If not, you can create a measure like this:

Measure =
VAR _skills =
    CALCULATETABLE ( VALUES ( demands[skill] ) )
VAR _matchingSkills =
    INTERSECT ( _skills, VALUES ( skills[skill] ) )
RETURN
    IF ( COUNTROWS ( _matchingSkills ) = COUNTROWS ( _skills ), 1, 0 )

and use this measure in the filter pane(setting Measure is 1) of the table visualization of the employees.

I have attached the mock-up file I created to demonstrate.

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[v-xicai]

Hi @renaudstaessens ,

You may create measure like DAX below, then put measure Filter1 in the Visual Level Filter of Table visual which is displaying Nane, Email and Skills of employees , and setting the Filter1 as "is not blank"

Filter=  IF( MAX('Skills'[Skill])  IN FILTERS('Staffing Need'[Skills] ), 1, BLANK()  )

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[renaudstaessens]

Hi @v-xicai thanks or your answer but it seems not to work 🤔...

I created 2 visual tables . the first one (the one that contains skills demand) . the second one which lists the email of the guy that are supposed to have all required skills correponding to a demand. Please note that this second visual doesn't list the skills only the email of the guy (otherwise i can have several lines for one single guy and i don't want to.  I added the visual filter the way you mentionned to the 2nd visual ( it's in french but means not blank).

Whatever row i select in the first one ( whatever demand i select ) i always have the 24 same results . so the list of people display do not depend on the skills needed for the demand selected ... Not sure to catch the logic of your measure. could you explain in few words ? ( i don't understand why you are a doing a max...)

[sturlaws · Accepted solution]

Hi @renaudstaessens 

have you managed to solve your issue?

If not, you can create a measure like this:

Measure =
VAR _skills =
    CALCULATETABLE ( VALUES ( demands[skill] ) )
VAR _matchingSkills =
    INTERSECT ( _skills, VALUES ( skills[skill] ) )
RETURN
    IF ( COUNTROWS ( _matchingSkills ) = COUNTROWS ( _skills ), 1, 0 )

and use this measure in the filter pane(setting Measure is 1) of the table visualization of the employees.

I have attached the mock-up file I created to demonstrate.

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[renaudstaessens · Accepted solution]

Hi @sturlaws  many thanks as your solution is working great and your explanations and examples are perfect !! sorry not to answer quickly ( i was on vacation 😃 )

[renaudstaessens]

it seems that i wrongly  accept my comment to your solution as the solution 😞 . Sorry it is the first time i accept a solution . I also accepted your own but didn't find a way to remove the first approval . Once again many many thanks for your great help!!! i ll check how to give "kudos"