Forum Discussion

Kallagan's avatar
Kallagan
Frequent Visitor
9 years ago
Solved

match 2 columns from 2 different tables

Hello

I'm new to Power BI and to the BI world in general.

 

I have to match 2 columns from 2 diffrent tables. One table contains the name of people in an organization and the other table contain the name of people who have participated to a training.

 

1 contains ~ 700 lines and the other ~ 100 lines.

 

The goal is to return true when people have attended the training. There is also a catch, some people have attended the same training 2 or 3 times.

 

When I try to merge, Power BI says that there is nothing to match between the 2 sets of data.

 

 

The sets of data looks like the folowing:

 

 

Table 1                                         Table 2

NAME                                           NAME

AA                                                 AJ

AB                                                 AJ

AC                                                 AA

AD                                                 AB

AE                                                 AB

AF                                                 AE

AG                                                AG

AH                                                AJ

AI

AJ

I've search for a while but the solution doesn't seem to work.

 

 

Thank you for your help

  • TomMartens's avatar
    TomMartens
    9 years ago

    Hey,

     

    can you please upload your pbix to onedrive or dropbox and then post the link.

     

    Here is a scrrenshot from my testdata

    I'm using this DAX statement to create the Calculated Column: HasAttended

    HasAttended = 
IF(
    ISBLANK(
        LOOKUPVALUE(trainings[Name],trainings[Name],'employees'[Name])
    )
    ,"No"
    ,"Yes"
)

     

    Regards

    Tom

5 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    9 years ago

    Hey,

     

    I would not merge both tables (means, after the merge there is just one table). Instead I would create a calculated column in the employees table like so

    Has attended a training = 
IF(
	ISBLANK(
		LOOKUPVALUE(Training[Column1],Training[Column1],'employee'[Column1])
	)
	,"NO"
	,"YES"
)

    Hopefully this what you are looking for.

     

    Regards

    Tom

     

    • Kallagan's avatar
      Kallagan
      Frequent Visitor
      9 years ago

      Hello Tom

       

      First of all thank you for your answer that is exactly what I was looking for. Sadly, the request only returns 4 results ("True in the calculated column) where I should have 91 "True".

       

       

      The employees that has returned true don't have anything special. Am I doing something wrong ?

      • TomMartens's avatar
        TomMartens
        Icon for Super User rankSuper User
        9 years ago

        Hey,

         

        can you please upload your pbix to onedrive or dropbox and then post the link.

         

        Here is a scrrenshot from my testdata

        I'm using this DAX statement to create the Calculated Column: HasAttended

        HasAttended = 
IF(
    ISBLANK(
        LOOKUPVALUE(trainings[Name],trainings[Name],'employees'[Name])
    )
    ,"No"
    ,"Yes"
)

         

        Regards

        Tom

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Kallagan,

     

    Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

     

    Best Regards!
    Dale