Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Please help.
Regards,
Abhi
This can be easily implemented in PowerQuery:
Hope this helps!
If I answered your question correctly please mark it as an accepted solution.
Thanks
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.
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.
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")
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsFor 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI am getting below error
"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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsIts different error 😞
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCan someone please help me with this post issue resolution.
@Anonymous ,
Try the below DAX Column:
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |