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 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 🙂 

  • 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], ", " )

     

5 Replies

  • v-zhangti's avatar
    v-zhangti
    Community Support
    2 years ago

    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

  • 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:

  • ERD's avatar
    ERD
    Community Champion
    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], ", " )