Power BI DAX required for lookup formula

[Kevin_Ds]

Hi All,

Please note there are 3 tables 
table 1 - Int 
table 2 -Gd
table 3 Exit

Main table is Int
result required in Int table by combininig all columns in one table against respective name.

P.s. - there is no unique columns (priamry key) in all 3 tables. Below table is consolidated in MS Excel, however in reality there are 3 tables. 

Int username	Int email id	Gd Name	UserPrincipalName	Primary address	Exit Name	Exit email id
Akash Rana	akash.rana$lasa	Rana Akash	b7ded$login.lasa	akash.rana$lasa	Rana Akash
	mumtaz.devi$lasa	Devi Mumtaz			Devi Mumtaz	mumtaz.devi$lasa

[v-junyant-msft · Accepted solution]

Hi  @Kevin_Ds ,

Oh sorry, I thought you were combining the GD and Exit tables into the Int table.

I need to remind you that the two words on this side of your data are in reverse order, which can cause serious problems during the creation of a new table, so please standardize the order of the words:

Here is the sample data:

Create a one-to-one link between the three tables with the Name field:

Please try to use this DAX to create a calculated table:

Merge_DAX = 
ADDCOLUMNS(
    'Int',
    "Int Email ID", 'Int'[email id],
    "UserPrincipalName", RELATED(Gb[UserPrincipalName]),
    "Primary address", RELATED(Gb[Primary address]),
    "Exit Email ID", RELATED(Exit[email id])
)

And the final output is as below:

Or you can use Power Query:
First merge Int and Gb:

And expand table:

Then merge this table and Exit and also expand:

The final output is the same:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-junyant-msft]

Hi @Kevin_Ds ,

You mention DAX in the title, but this is the Power Query forum. And based on your description it seems that Power Query would make it easier to fulfill your needs.

Please check whether this is what you want?
Merge Table Gb and Table Exit to get Table Int:

And the final output of Table Int is as below:

If this is not the result you are looking for, could you please provide your data in a clearer format such as a screenshot in excel, the format of the sample data you are currently providing is a bit confusing I can't clearly understand what you are looking for.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kevin_Ds]

Hi @v-junyant-msft 

Thanks for the suggestions.. However I have attached the snapshot for the query and the result. Could you please advise or steps to get the desired results. 

thanks in advance. 

[v-junyant-msft · Accepted solution]

Hi  @Kevin_Ds ,

Oh sorry, I thought you were combining the GD and Exit tables into the Int table.

I need to remind you that the two words on this side of your data are in reverse order, which can cause serious problems during the creation of a new table, so please standardize the order of the words:

Here is the sample data:

Create a one-to-one link between the three tables with the Name field:

Please try to use this DAX to create a calculated table:

Merge_DAX = 
ADDCOLUMNS(
    'Int',
    "Int Email ID", 'Int'[email id],
    "UserPrincipalName", RELATED(Gb[UserPrincipalName]),
    "Primary address", RELATED(Gb[Primary address]),
    "Exit Email ID", RELATED(Exit[email id])
)

And the final output is as below:

Or you can use Power Query:
First merge Int and Gb:

And expand table:

Then merge this table and Exit and also expand:

The final output is the same:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kevin_Ds]

HI @v-junyant-msft 

thanks for the steps.. however my only concern is how to match the names in all 3 tables.. The data is huge in volume and the Int table has reverse name of the employees compare to GD and Exit table

[v-junyant-msft · Accepted solution]

Hi @Kevin_Ds ,

DAX can't do anything to match two names in opposite order, I think you may only be able to do that using fuzzy matching in Power Query.
Int:

Gb:

Exit:

As before, we begin by merging table Int and table Gb.

And in the Fuzzy matching options:

The number depends on the length of the name with the longest string length in the table. This number needs to be greater than or equal to this length.

And then merge Exit, the same as before:

And the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.