How to create multiple dynamic tables of employees and counting unique employees from all of them

[HassanAshas]

Hi, I am working on Employee Log Sheet submissions problem. 

I have a dataset like this, 

Emp_ID	Name	Project	Week	Status
1	John	A	1	Drafted
1	John	A	2	Missing
1	John	A	3	Missing
2	Leo	A	1	Submitted
2	Leo	B	1	Submitted
2	Leo	A	2	Submitted
2	Leo	B	2	Submitted
2	Leo	A	3	Missing
2	Leo	B	3	Missing
3	Alissa	C	1	Missing
3	Alissa	C	2	Missing
3	Alissa	C	3	Missing
4	Emma	C	1	Missing
4	Emma	B	1	Submitted
4	Emma	C	2	Missing
4	Emma	B	2	Missing
4	Emma	C	3	Missing
4	Emma	B	3	Drafted

• It is possible for one employee to be working on multiple projects, like Emma and Leo in the above dataset.
• The "Status" column states whether the person submitted his logsheet or not. This column has three possible values, "Submitted", "Drafted" and "Missing"
• Employees submit logsheets on weekly basis. 

My problem is to count the unique number of employees who did not submit their log sheet. Now it is possible that one employee might be on two projects and he submitted logsheet for one project but didn't for the other project (like Emma in Week 1). So in such a case, this employee will be considered as the one who "submitted his logsheet" and will not count towards people who missed their logsheet. 

Similarly, if a person has "Drafted" his logsheet then again, it will be considered as "missed" timesheet. 

So, to tackle this problem, I made the following DAX to calculate the unique number of employees who missed their Log sheet for a certain week, 

Missing Logsheets = 


var text_value = "Submitted"

VAR _Filtered_Table = 
    FILTER(
        'LogSheet', 
        'LogSheet'[Status] = text_value
    )

VAR _Not_Submitted_EmpIDs = 
    EXCEPT(
        VALUES('LogSheet'[Emp_ID]),
        CALCULATETABLE(
            VALUES('LogSheet'[Emp_ID]),
            _Filtered_Table
        )
    )

VAR _Result = COUNTROWS(_Not_Submitted_EmpIDs)

return _Result

This DAX basically finds all the people who submitted their logsheets (irrespective of whether they had any row with a "missing/drafted" Logsheet) and then using that list of Employees, finds the employees who missed their logsheet and take the count of that table. 

This works fine for one selected week. 

The problem comes when I filter the results by multiple weeks. 

In this case, my requirement slightly changes. 

I need to calculate the unique number of employees who missed logsheet in any of the weeks currently selected. 

So, for example, if I have selected "Two weeks" (let's say 1 and 2), and one employee submitted his logsheet in the first week, but missed his logsheet in the 2nd week, then he should also count towards the Employees who missed the logsheet submission (and because he submitted the logsheet in first week, so he will count towards the Employees who submitted logsheets, as well. But this is easy. Main problem comes in Missing Logsheet Employees) 

For example, 

If 2 weeks are selected, 

then the Employee "Emma" will count towards "Missing Logsheet Employees" because she didn't submit her logsheet in the week 2 (even though she did submit her logsheet in the first week) 

Similarly, if all 3 weeks are selected, then total number of employees who missed logsheet will be 4 because none of the employees have submitted their logsheet in the third week. 

How can I tackle this problem? Can anyone help? 

The way I see it, I have already created DAX to find the employees who missed their logsheet for one week. 

I need to create a variable that can store summarized results against each week. And then I can combine all those results (that is, list of employees missing their logsheet for each week) and then take the unique count of that combined table. 

But I can't seem to find any way to solve this problem.

If you would like to download the Example Power BI File, you may do so from here: https://drive.google.com/file/d/1vDRG2X4JegnMQFC0GhGaUpUwbP0ThK6E/view?usp=sharing

[Greg_Deckler · Accepted solution]

@HassanAshas Try:

Missing Any Week Distinct Count = 
    VAR __MissingStatusTable = { "Missing", "Draft" }
    VAR __Table = FILTER( 'LogSheet', [Status] IN __MissingStatusTable )
    VAR __Result = COUNTROWS( DISTINCT( SELECTCOLUMNS( __Table, "Emp_ID", [Emp_ID] ) ) )
RETURN
    __Result

[HassanAshas]

Hi @Greg_Deckler 
Seems like there was a small typo, and the DAX actually kind of fails at one point. 
If you have the time, could you please help out in this, 

In your DAX, you used "Draft", which was a small typo and in actual, it should have been "Drafted"

Due to this change, when I selected "Week 1", it showed me 2 employees who missed the logsheet submission (which was the correct result) but using your DAX (with the typo corrected), the result actually comes out to be 3 employees. 

The reason actually is the Employee with ID = 4, Name: Emma

For Emma, when you filter the Table by {"Missing", "Drafted"}, it actually takes her record in as well and will count her towards the unique employees who missed the logsheet. 

This is incorrect behavior and she shouldn't be part of the final solution. 

Only the Emp ID = 1 (John) and Emp ID = 3 (Alissa) should be part of the people who missed the timesheet when considering Week 1 only. 

Is there any way to improve the DAX? I would be very thankful to you. 

[HassanAshas]

@Greg_Deckler  Oh my god! You just gave me a totally different way to approach the problem. 
And it works perfectly! 
Thank you so much! 
It's amazingly clear and precise solution and also avoids the use of Calculate function altogether, making it even more fantastic. I greatly appreciate the help!