Forum Discussion
Dynamic filtering table with no physical relationship
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
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.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 😃 )
5 Replies
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.
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...)
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.
