filtering between two tables

[sharpers4567]

Hi guys,I am relatively new to powerbi and am struggling to implement this filter.
I have two tables, One with userID and one with their different qualifications, another table with different Jobs and the required qualification per job, my data looks something similar to below:

I would like to match up the user to each Job they are qualified to do and hence be able to filter for each job to show the necessary users and qualifications.

I have tried a various approaches such as lookupvale, but as there can be multiple jobs available for different qualification i couldnt find a way around it. 
i tried to use concatenx:

Concatenx ( summarize(  filter( JOBS, JOBS[Requirement} = User[qualification] ) , JOBS[job] ) , JOBS[job] , ", ")

But this just gave me a list of the jobs that the qualifications Could get not what the user is qualified to get and unsure on how to filter the orignal table?

Hope this makes sense and Any help would be much appreciated and thank you in advance for any advice 🙂 

[v-zhangti]

Hi, @sharpers4567 

Has your problem been solved yet? If it is solved please accept the reply that offered you help as the solution. If it is not solved, please tell us what output you expect?

Best Regards

[ERD · Accepted solution]

Hi @sharpers4567 , you can try this measure:

jobs = 
var t = 
    FILTER (
        GENERATE ( VALUES ( users[user] ), VALUES ( jobs[job] ) ),
            VAR current_user = CALCULATE ( MAX ( users[user] ) )
            VAR current_job = CALCULATE ( MAX ( jobs[job] ) )
            VAR qualifications = CALCULATETABLE ( VALUES ( users[qualification] ), users[user] = current_user )
            VAR requirements = CALCULATETABLE ( VALUES ( jobs[requirement] ), jobs[job] = current_job )
            VAR check = COUNTROWS ( EXCEPT ( requirements, qualifications ) )
            RETURN
                check = blank()
    )
RETURN
    CONCATENATEX( t, [job], ", " )

[FreemanZ]

hi @sharpers4567 ,

try to plot a table visual with table1[user] column and a measure like:

JobList = 
VAR _list = 
CALCULATETABLE(
    VALUES(Table2[job]),
    TREATAS(
        VALUES(Table2[Requirement]),
        Table1[Qualification]
    )
)
VAR _result = 
CONCATENATEX(
    _list,
    Table2[job],
    ", "
)
RETURN _result

plot a table visual with table2[requirement] column and a measure like:

UserList = 
VAR _list =
CALCULATETABLE(
    VALUES(Table1[user]),
    TREATAS(
        VALUES(Table1[Qualification]),
        Table2[Requirement]
    )
)
VAR _result =
CONCATENATEX(
    _list,
    Table1[user],
    ", "
)
RETURN _result

it worked like:

[sharpers4567]

Hi, Thank you for the solution, it looks like its heading in the right direction, However I am trying to show the users who match fully, for example for job 1 you need all x,y,z so only user 'a' would be qualified for that sepecific one, sorry for the confusion.

[FreemanZ]

hi @sharpers4567 ,

try to plot a table visual with table2[job] column and a measure like:

QualUserList = 
VAR _reqlist = 
CONCATENATEX(
    VALUES(table2[Requirement]), 
    table2[Requirement], 
    ", "
)
VAR _table =
ADDCOLUMNS(
    VALUES(table1[user]),
    "QuaList",
    CONCATENATEX(
        CALCULATETABLE(VALUES(table1[qualification])),
        table1[qualification],
        ", "
    )
)
VAR _userlist =
CALCULATETABLE(
    VALUES(table1[user]),
    FILTER(
        _table,
        CONTAINSSTRING([QuaList], _reqlist)
    )
)
VAR _result =
CONCATENATEX(
    _userlist,
    table1[user],
    ", " 
)
RETURN _result

it worked like: