Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Power BI DAX required for lookup formula

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 usernameInt email idGd NameUserPrincipalNamePrimary addressExit NameExit email id
Akash Ranaakash.rana$lasaRana Akashb7ded$login.lasaakash.rana$lasaRana Akash 
 mumtaz.devi$lasaDevi Mumtaz  Devi Mumtazmumtaz.devi$lasa
2 ACCEPTED SOLUTIONS

Hi  @Anonymous ,

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:

vjunyantmsft_5-1714118966773.png
vjunyantmsft_6-1714118975980.png

vjunyantmsft_7-1714118987079.png



Here is the sample data:

vjunyantmsft_0-1714118790304.png
vjunyantmsft_1-1714118798024.png

vjunyantmsft_2-1714118804003.png

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

vjunyantmsft_3-1714118827554.png


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:

vjunyantmsft_4-1714118888458.png


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

vjunyantmsft_8-1714119046765.png

And expand table:

vjunyantmsft_9-1714119073075.png

Then merge this table and Exit and also expand:

vjunyantmsft_10-1714119106587.png

vjunyantmsft_11-1714119116018.png

The final output is the same:

vjunyantmsft_12-1714119137030.png


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.

View solution in original post

Hi @Anonymous ,

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:

vjunyantmsft_0-1714120286433.png


Gb:

vjunyantmsft_1-1714120296471.png


Exit:

vjunyantmsft_2-1714120307987.png


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

vjunyantmsft_3-1714120406650.png
And in the Fuzzy matching options:

vjunyantmsft_4-1714120437601.png

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.

vjunyantmsft_5-1714120547390.png

vjunyantmsft_6-1714120557148.png

 

And then merge Exit, the same as before:

vjunyantmsft_7-1714120604316.png

vjunyantmsft_8-1714120625092.png

vjunyantmsft_9-1714120636041.png

 

And the final output is as below:

vjunyantmsft_10-1714120656167.png

 

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.

View solution in original post

5 REPLIES 5
v-junyant-msft
Community Support
Community Support

Hi @Anonymous ,

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:

vjunyantmsft_0-1714093588753.png

vjunyantmsft_1-1714093662593.png

vjunyantmsft_2-1714093704759.png

vjunyantmsft_3-1714093729243.png

And the final output of Table Int is as below:

vjunyantmsft_4-1714093788769.png


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.

Anonymous
Not applicable

 

 

 

 

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. Kevin_Ds_0-1714116589470.png

 

Hi  @Anonymous ,

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:

vjunyantmsft_5-1714118966773.png
vjunyantmsft_6-1714118975980.png

vjunyantmsft_7-1714118987079.png



Here is the sample data:

vjunyantmsft_0-1714118790304.png
vjunyantmsft_1-1714118798024.png

vjunyantmsft_2-1714118804003.png

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

vjunyantmsft_3-1714118827554.png


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:

vjunyantmsft_4-1714118888458.png


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

vjunyantmsft_8-1714119046765.png

And expand table:

vjunyantmsft_9-1714119073075.png

Then merge this table and Exit and also expand:

vjunyantmsft_10-1714119106587.png

vjunyantmsft_11-1714119116018.png

The final output is the same:

vjunyantmsft_12-1714119137030.png


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.

Anonymous
Not applicable

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

Hi @Anonymous ,

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:

vjunyantmsft_0-1714120286433.png


Gb:

vjunyantmsft_1-1714120296471.png


Exit:

vjunyantmsft_2-1714120307987.png


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

vjunyantmsft_3-1714120406650.png
And in the Fuzzy matching options:

vjunyantmsft_4-1714120437601.png

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.

vjunyantmsft_5-1714120547390.png

vjunyantmsft_6-1714120557148.png

 

And then merge Exit, the same as before:

vjunyantmsft_7-1714120604316.png

vjunyantmsft_8-1714120625092.png

vjunyantmsft_9-1714120636041.png

 

And the final output is as below:

vjunyantmsft_10-1714120656167.png

 

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors