Forum Discussion

Tcerginer's avatar
Tcerginer
Regular Visitor
9 years ago
Solved

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...
Need Help
  • BhaveshPatel's avatar
    BhaveshPatel
    9 years ago

    Another solution using Query Editor.

     

    1. Left Outer Join

    2. Conditional Column.

     

    See the attached screenshots.

    LEFT OUTER JOINEXPAND COLUMNCONDITIONAL COLUMNFINAL OUTPUT

v-caliao-msft's avatar
v-caliao-msft
Microsoft Employee
9 years ago

Hi Tcerginer,

 

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

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

 

Regards,

Charlie Liao

 

Tcerginer's avatar
Tcerginer
Regular Visitor
9 years ago

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

 

 

 

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's avatar
    Stachu
    Community Champion
    9 years ago

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

    1) merging Coworkers with Trail

    2) merging 1) with Graduated