Forum Discussion

sharpers4567's avatar
sharpers4567
Frequent Visitor
2 years ago
Solved

filtering between two tables

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 a...
  • ERD's avatar
    ERD
    2 years ago

    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's avatar
FreemanZ
Super User
2 years ago

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's avatar
sharpers4567
Frequent Visitor
2 years ago

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's avatar
    FreemanZ
    Super User
    2 years ago

    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: