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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculated column to find email address from multiple options

Dear All,

I need you help in creating a calculated column named "M_Email". This is typical employee manager relationship where a manger is also an emaployee.
If an employees role is defined as "Manager" in Role column then in "M_Email" column that respective email address should be populated and for rest it should be blank.

Below is the screen shot of some sample data where we need to have last column "M_Email" as calculated column.

 

Note: field role is already a calculated column.

 

Abhi_hgt_0-1602840336501.png

 

 

Please help.

 

Regards,
Abhi

18 REPLIES 18
akul
Helper I
Helper I

This can be easily implemented in PowerQuery:

 

  1. Say your current table is A. Build a duplicate table B out of A.
  2. In table B filter Role = Manager
  3. Back to Table A. Merge with Table B on A.Mname = B.Ename
  4. In expand options, select E_Email.
  5. Rename TableB.E_Email to M_Email

Hope this helps!
If I answered your question correctly please mark it as an accepted solution.

Thanks

Anonymous
Not applicable

Hi Akul,

 

Thank you for the reply.

I might have missed in details that the field Role is a calculated column so if we try to use your solution in query editor then Role column will not be available.

 

Do think there could be be other approch to it?

 

Regards,

Abhishek

Try the following DAX:

LOOKUPVALUE(
    'Table'[email],
    'Table'[ename],
    'Table'[mname]
)


Similar to the VLOOKUP in Excel. 

Anonymous
Not applicable

Hi Akul,

Thanks for the reply, the DAX you praposed wont work as one manager will have multiple different email address and we need to select the perticular email which have role defined as Manager.

In proposed DAX I am getting error as it have multiple matching.

I defined the scenarion in original post also in picture with color to help.

Hope you could help me.

Regards,

Abhishek

Can you provide a sample dataset in a PBIX file? Maybe also if you can add the DAX  in it with the error.

Anonymous
Not applicable

Hi Akul,

 

Due to confidantial information from client, I will not be able to share PBIX file. Sorry for that.

Note : The Mid of the manager is also the Eid of manager.

 

Regards,

Abhishek

Hi @Anonymous ,

 

Looking at your data and without additional information if your e-mail from the managers is unique, meaning you don't have more than one e-mail marked as manager for the same user then you just need to add one additional parameter to the lookup, that is the Role that you are looking for:

 

Manager_Mail = LOOKUPVALUE('Table'[E_Email];'Table'[Ename];'Table'[Mname];'Table'[Role];"Manager")

 

MFelix_0-1603814317747.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

For me this DAX is not working, it is not taking 3rd parameter as column name and asking to for serch value.

Hi @Anonymous ,

 

You are using it has a calculated column correct?

 

Be aware that you may need to change the ";" by "," on your syntax.  Can you share a print screen of the error and the syntax you are using?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

I am getting below error

 

Abhi_hgt_0-1603903020559.png

"Expressions that yield variant data-type cannot be used to define calculated columns."

Hi @Anonymous ,

 

In your last syntax you must not place the = you must place the "Sales Manager" as a parameter in your syntax rewrite the code to:

 

Parent User Email =
LOOKUPVALUE (
    User[internalemailadress],
    User[fullname], Use[parentsystemuseridyominame],
    User[Role Name], "Sales Manager"
)

Don't know if I have the names correctly copied from the image but this should work.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Its different error 😞

 

Abhi_hgt_0-1603903658100.png

Parent User Email = LOOKUPVALUE(User[internalemailaddress],User[fullname],User[parentsystemuseridyominame],'User'[Role Name], "Sales Manager")
 
Error : "A table of multiple values was supplied where a single value was expected."

Hi @Anonymous ,

 

One question is the Role coming from a different table? asking this because in your formula you have all the columns without the '' and the table on the user role is 'User'?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi MFelix,

All the columns are from same table.

 

Regards,

Abhi

Are you certain that for the same user there is only one row with sales manager role?

 

Believe that the issue is related with the fact that is returning more that one values on the lookup so it does not return correct values


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Can someone please help me with this post issue resolution.

Tahreem24
Super User
Super User

@Anonymous ,

Try the below DAX Column:

M_EMail Column = IF(Tbl1[Role]="Manager",Tbl1[Memail],Tbl1[Eemail])
Capture.PNG
P.S.:  I put just single character under both Email ID Columns.
 
 
 
 
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
Anonymous
Not applicable

Thank you for the reply but it does not meet the requirement.

We need to derive M_email which you already used in calculated column as a condition and we have many different email address for user and need to select specific email address by considering role column value.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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