Need help with writing DAX for following scenario

[Anonymous]

Help needed for writing DAX for following scenario: (Please refer to the UPDATE section in the question, there is some change in the requirement). Thanks!!!

I have a dataset that contains information about 

• Employee Name
• His/Her Manager 
• Manager's Manager (aka TEM Member)
• Course 
• Status of the Course (Completed/Not Completed)
• Data is as follows: (Also attached excel file with data)

Expected Result:

What I am looking for is: I would like to calculate the count of employees (Employee+ including manager, +TEM Member him/herself) who has completed the course under each TEM Member. 

• For example purpose, assume that Bettie is a team member (Manager's Manager) and three people (Alan, Mike, Rogers) report to her.
• While here, Rogers himself is a manager and he reports to Bettie.
• Bettie does not have any manager.
• Alan and Mike reports to Rogers.

Now, here I would like to see the count of employees completed for each course (Includes Employee+Manager+TEM Member). For example, if there is a Science course, then this course has to be completed by Bettie and her team (Rogers,Alan, Mike). From above screenshot, as only Alan, Mike and Bettie completed the course, count would be 3.

Please help me in writing DAX for this scenario.

Pasted sample data 

Employee	Manager	TEM Member	Courses	Status
Alan	Rogers	Bettie	Math	Not Completed
Alan	Rogers	Bettie	Science	Completed
Mike	Rogers	Bettie	Science	Completed
Mike	Rogers	Bettie	Math	Not Completed
Rogers	Bettie	Bettie	Math	Completed
Rogers	Bettie	Bettie	Science	Not Completed
Bettie	NA	Bettie	Math	Completed
Bettie	NA	Bettie	Science	Completed

Thanks in advance!!!

UPDATE:

 Actually, there is some change in the requirement. I realized it later. This TEM member is in another table. the common column between these two tables is employee number.

Important thing to consider: In the manager column, there would be manager above Bettie to whom she reports to, but the loop should stop at the level of Bettie, it should not go beyond that.

(Michael (Manager Level 3)>> Bettie (Level 2) >> Rogers (Level 1) >> Alan and Mike (Employee)

this loop should stop only till Bettie and her name should be populated in TEM column across all the employees that are under her  (Rogers,Alan Mike)

For example:

Right now, EMPLOYEE table is in this way:

EmployeeNumber	Employee	Manager	Courses	Status
1	Alan	Rogers	Math	Not Completed
1	Alan	Rogers	Science	Completed
2	Mike	Rogers	Science	Completed
2	Mike	Rogers	Math	Not Completed
3	Rogers	Bettie	Math	Completed
3	Rogers	Bettie	Science	Not Completed
4	Bettie	NA	Math	Completed
4	Bettie	NA	Science	Completed

Screen shot:

There is another table for TEM Member as below:

Employee Number	TEM Member
4	Bettie

Required table in this way:

EmployeeNumber	Employee	Manager	TEM Member	Courses	Status
1	Alan	Rogers	Bettie	Math	Not Completed
1	Alan	Rogers	Bettie	Science	Completed
2	Mike	Rogers	Bettie	Science	Completed
2	Mike	Rogers	Bettie	Math	Not Completed
3	Rogers	Bettie	Bettie	Math	Completed
3	Rogers	Bettie	Bettie	Science	Not Completed
4	Bettie	NA	Bettie	Math	Completed
4	Bettie	NA	Bettie	Science	Completed

Screenshot:

I have to get TEM Member Bettie name shown across all the employee who work under her, 1 level hierarchy or 2 level hierarchy down.  For example (Her name should be shown across Rogers (who directly reports to her) and Alan, Mike (who reports to Rogers)

Then I need to calculate the results as below:

Can you guys please help me in solving this. It would be very much helpful. I am trying since one day to get the name of TEM Member  Bettie across all the employees (including rogers (Who reports directly to Bettie and Alan, Mike who reports to Rogers and not directly to Bettie)

Thanks so much again!!!

[Anonymous]

Basically, i want it to loop for example,

let's say Roger's manager is Bettie so her name should be across Rogers name column, and after that, alan and mike report to roger's so, even for alan and mike, bettie's name should be shown across the column.

I am trying to figure out a way to write dax for the column but i could not do that.

I was able to show Bettie name across Rogers but not able to write logic to show it across alan and mike

[Anonymous]

Hi,

Thanks again @AlB  !

But when I pull this BET column from BET table across Employee table, it's being populated only for few values. For example (in the actual data), let say i pulled Bettie from the column then it's being shown a across for employee name Bettie and Rogers. Not for the other names.

with the below dax for Example

Employee   Employee Manager Name    BET member   Actually required

Mike           Rogers                                                            Bettie

Rogers        Bettie                                   Bettie                 Bettie

Alan           Rogers                                                            Bettie

Bettie        Leslie                                                               Bettie

BET Member = CALCULATE(FIRSTNONBLANK('BET'[BET Name],TRUE()),FILTER('BETTable',EmployeeTable[Employee  Manager Name]='BET Table'[Employee BET Name]))
I tried to write DAX for a custom column 

With this DAX i am getting the name Bettie only across Rogers not across Alan, Mike as they don't have manager name as bettie while rogers only has bettie

Can you please help me in writing DAX. 

I need the DAX to create calculated column to 

1. compare BET name (Bettie) with Employee Manager name (Rogers) then it should

2. also write Bettie name across employees who has Manager name as Rogers because ultimately it's the bettie who is manager to also the rogers.

[AlB]

@Anonymous 

See it all at work in the attached file.

You need a relationship between the tables and the rest is pretty much the same as what we did earlier

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Anonymous]

I have two tables one is employee and the other is TEM member. the common column between these two tables is employee number.

For example:

Right now, EMPLOYEE table is in this way:

EmployeeNumber	Employee	Manager	Courses	Status
1	Alan	Rogers	Math	Not Completed
1	Alan	Rogers	Science	Completed
2	Mike	Rogers	Science	Completed
2	Mike	Rogers	Math	Not Completed
3	Rogers	Bettie	Math	Completed
3	Rogers	Bettie	Science	Not Completed
4	Bettie	NA	Math	Completed
4	Bettie	NA	Science	Completed

Screen shot:

There is another table for TEM Member as below:

Employee Number	TEM Member
4	Bettie

Required table in this way:

EmployeeNumber	Employee	Manager	TEM Member	Courses	Status
1	Alan	Rogers	Bettie	Math	Not Completed
1	Alan	Rogers	Bettie	Science	Completed
2	Mike	Rogers	Bettie	Science	Completed
2	Mike	Rogers	Bettie	Math	Not Completed
3	Rogers	Bettie	Bettie	Math	Completed
3	Rogers	Bettie	Bettie	Science	Not Completed
4	Bettie	NA	Bettie	Math	Completed
4	Bettie	NA	Bettie	Science	Completed

Screenshot:

I have to get TEM Member Bettie name shown across all the employee who work under her, 1 level hierarchy or 2 level hierarchy down.  For example (Her name should be shown across Rogers (who directly reports to her) and Alan, Mike (who reports to Rogers)

Then I need to calculate the results as below:

please help me in solving this. It would be very much helpful. I am trying since one day to get the name of TEM Member  Bettie across all the employees (including rogers (Who reports directly to Bettie and Alan, Mike who reports to Rogers and not directly to Bettie) but i am not successfull

[AlB]

Hi @Anonymous 

A very simple solution would be to place TEM Member and Course in a table visual,  a slicer on Status and select "Completed"  and then place the Count of Status (with an implicit or explicit measure) in the visual.

See it all at work in the attached file.   You could also embed the "Complete" filtering in the measure, so that there would be no need for the slicer

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Anonymous]

@AlB Thank you so much for the reply!! 

Actually there is some small change in the requirement as I posted under UPDATE in the question. I tried to post the update here but it's not being allowed to reply here. Can you please help me in solving it @AlB  I am trying since yesterday for many hours but could not solve it.