How many employees attended client training? Please advise.

[Anonymous]

We need to find out how many internal employees attended client training. How can I pick the employee out of the Sign Ups (based on Email matching on both tables?) Then, I need to calculate the sum of employees that attended the client trainings.
In other words, how do I use 'Email' from both tables to determine which of the students who signed up are internal empoyees? If the student is an employee, the email should appear on both "Client Training Sign Up" and on the "Worker List".	"HR Worker List"= all Internal Employees  "Client Training Sign Up" = both internal employees and external client enrollments in client trainings

"Client Training Sign Up" table headers

 "Worker List" table headers

[v-diye-msft]

Hi @Anonymous 

If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly.thanks!

[Anonymous]

Have you tried adding a calculated column like this to your Client Training Sign-up table?

IsEmployee = 
IF (
    ISBLANK ( LOOKUPVALUE ( WorkerList[Email], ClientTrainingSignUp[Email], ClientTrainingSignUp[Email] ) ),
    "External Clients",
    "Internal Employees"
)

I think a calculated column like this and subsequently creating measures to count the "External Clients" or "Internal Employees"  will give you the expected results.

[Anonymous]

@v-gizhi-msft  hi, do you have any guidance?

[Greg_Deckler]

You could create a measure that tests if the email is IN the email of the internal worker table. Sample data and expected result would be helpful. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

@Greg_Deckler Greg, thanks for responding and the reminder on how to post. My files have sensitive data, and I'm blocked from downlading online filed containing a solution. Can you use the name of the tables and the column headers to help me? I am new so I am not sure how to create a measure with the IN operator in DAX... but I did try. Will you please explain steps with screenshots?
Desired result: Please see the image I added in the orginal post.

[Greg_Deckler]

Another thought on this, here is a blog article I wrote that you may find useful as it solved a similar issue:

https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

[Greg_Deckler]

Not asking for you to share sensitive data but I am asking that you save me some work to try to help you. I just need some sample data mocked up that I can copy and paste into Power BI. Otherwise I have to invent a bunch of data and do a bunch of typing, and I do enough typing during the day.

All I need is enough sample data to replicate the problem.