The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
Hi all,
next daft moment I need help with 🙂
I have a table that contains employee information that I use to gather data for mass mailings. Mostly I need this table to provide the first names. The relationship to other tables is the eMail.
Name | |
jane.doe@company.com | Jane |
joe.doe@company.com | Joe |
jenny.doe@company.com | Jenny |
Jack.doe@company.com | Jack |
Maggy.doe@company.com | Maggy |
Marcus.doe@company.com | Marcus |
This table also contains the persons Manager as eMail adress:
Name | Manager | |
jane.doe@company.com | Jane | Maggy.doe@company.com |
joe.doe@company.com | Joe | Maggy.doe@company.com |
jenny.doe@company.com | Jenny | Marcus.doe@company.com |
Jack.doe@company.com | Jack | Marcus.doe@company.com |
Maggy.doe@company.com | Maggy | Ceo.doe@company.com |
Marcus.doe@company.com | Marcus | Ceo.doe@company.com |
Now sometimes I may need to adress mass mailings to the Manager of the person and require their first name.
Now that's where I'm stuck. Any way to create a new column "Manager Name" that takes "Manager", looks it up in "eMail" and returns the corresponding value in "Name"?
Name | Manager | Manager Name | |
jane.doe@company.com | Jane | Maggy.doe@company.com | Maggy |
joe.doe@company.com | Joe | Maggy.doe@company.com | Maggy |
jenny.doe@company.com | Jenny | Marcus.doe@company.com | Marcus |
Jack.doe@company.com | Jack | Marcus.doe@company.com | Marcus |
Maggy.doe@company.com | Maggy | Ceo.doe@company.com | CEO |
Marcus.doe@company.com | Marcus | Ceo.doe@company.com | CEO |
I guess it'll have to be a new table. I tried creating a DISTINCT table with "Manager" and get their names. Works fine until I bring in the "eMail" column again. Then it displays the employees names under that manager instead of their own:
Manager | Name |
Maggy.doe@company.com | Jane |
Maggy.doe@company.com | Joe |
Marcus.doe@company.com | Jenny |
Marcus.doe@company.com | Jack |
Ceo.doe@company.com | Maggy |
Ceo.doe@company.com | Marcus |
Can someone point me in the right direction?
Much appreciated 🙂
Solved! Go to Solution.
@PuddleRunna If you want them as two tables you can duplicate and relate them on Manager[Email] to Employee[Manager] as per the attached file.
You could also do a Merge in Power Query to make this 1 flat table.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@PuddleRunna If you want them as two tables you can duplicate and relate them on Manager[Email] to Employee[Manager] as per the attached file.
You could also do a Merge in Power Query to make this 1 flat table.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.
User | Count |
---|---|
158 | |
106 | |
96 | |
83 | |
75 |
User | Count |
---|---|
154 | |
137 | |
131 | |
81 | |
62 |