cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tcerginer
Regular Visitor

MATCH between 2 tables

Hello Community,

 

I am trying to crease a measure using something similar to the MATCH function from excel.

 

I have a table(1) that tells me which of the company's training modules my coworkers have completed. Another relationship between tables(2) tells me which modules each coworker must take. I would like to use something like MATCH that would scan table 2 and tells me if there is a match in table 1, if true it would return me as "Completed" and if false "Due".

 

Below there is an image to help understanding

 

 http://imgur.com/a/W27mi

 

I tried using CONTAINS, but did not succeed.

 

Appreciate your help!

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Another solution using Query Editor.

 

1. Left Outer Join

2. Conditional Column.

 

See the attached screenshots.

LEFT OUTER JOINLEFT OUTER JOINEXPAND COLUMNEXPAND COLUMNCONDITIONAL COLUMNCONDITIONAL COLUMNFINAL OUTPUTFINAL OUTPUT

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

View solution in original post

7 REPLIES 7
v-caliao-msft
Microsoft
Microsoft

Hi @Tcerginer,

 

Tested it on my local environment using the sample data below.
Untitled.png

We can get the the expected result by using the DAX below.

Column = IF(ISBLANK(LOOKUPVALUE(Table1[Coworkers],Table1[Modules ],Table2[ModulesMustTake],Table1[Coworkers],Table2[Coworkers])),"Due","Completed")
Capture.PNG

 

Regards,

Charlie Liao

 

Thank you very much @v-caliao-msft @Greg_Deckler and @BhaveshPatel for helping me! I'm new with PowerBI and I am self teaching Relational Database from scratch, you are really helping me out! Your solutions aren´t working yet, because the problem is a bit more elaborated and I should have been more specific.

 

As the company I am working have many employees, the modules that must be taken depends on the employee's function and those modules are susceptible of change over time, I created the relational model below:

 

Obs: 'trail' means all the modules that a function should take. The doctor's trail is different from engineer's trail..

 

Capturar1.JPGCapturar2.JPG

 

 

As you can see, I dont have this 'table2' to use the LOOKUPVALUE. But we know that the tables 'coworkers' and 'trail' are related through table 'function'. Is it possible to generate this 'table2' using Dax or queries so we could use lookupvalue?

 

Another point:

 

It is possible for a coworker to take modules that aren´t on their trails! A Teacher could take Math if wanted. Those would be displayed in another separated table, as they aren´t obligatory.

 

I hope could make myself clear. English is not my mother language.

 

Thanks again for the support!

 

Tom

 

Stachu
Community Champion
Community Champion

@BhaveshPatel solution will work, you just need to do it 2 times

1) merging Coworkers with Trail

2) merging 1) with Graduated



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Thank you very much @Stachu @BhaveshPatel@v-caliao-msft!!

 

Both solutions helped me a lot!  It is working now 😄

Another solution using Query Editor.

 

1. Left Outer Join

2. Conditional Column.

 

See the attached screenshots.

LEFT OUTER JOINLEFT OUTER JOINEXPAND COLUMNEXPAND COLUMNCONDITIONAL COLUMNCONDITIONAL COLUMNFINAL OUTPUTFINAL OUTPUT

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
BhaveshPatel
Community Champion
Community Champion

You can use "LEFT OUTER JOIN" in Merge Queries in Query Editor to get the desired results. 

 

It includes all the rows in the Courses table in the results, whether or not there is a match on the CourseID column in the Coursecompletionstatus table.  Where there is no matching CourseID for a Course, the row contains a null value.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
Greg_Deckler
Super User
Super User

Perhaps one of the techniques listed here will help:

 

http://community.powerbi.com/t5/Community-Blog/Good-Ol-VLOOKUP-The-Ultimate-Guide-to-Lookups-in-Powe...

 

 


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors