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

     

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