topic filtering between two tables in DAX Commands and Tips

filtering between two tables

sharpers4567 — Fri, 03 Nov 2023 19:33:18 GMT

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 🙂

Re: filtering between two tables

FreemanZ — Sat, 04 Nov 2023 03:34:40 GMT

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:

Re: filtering between two tables

sharpers4567 — Sat, 04 Nov 2023 16:24:30 GMT

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.

Re: filtering between two tables

ERD — Sun, 05 Nov 2023 14:24:00 GMT

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

Re: filtering between two tables

FreemanZ — Mon, 06 Nov 2023 03:35:57 GMT

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:

Re: filtering between two tables

v-zhangti — Wed, 08 Nov 2023 09:09:39 GMT

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