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

[johnt75 · Accepted solution]

I think the below works

Missing = 
VAR Submitted = CALCULATETABLE(
	SUMMARIZE( 'LogSheet', 'LogSheet'[Emp_ID], 'LogSheet'[Week]),
	'LogSheet'[Status] = "Submitted"
)
VAR AllCombinations = CROSSJOIN(
	ALL('LogSheet'[Emp_ID]),
	ALLSELECTED( 'LogSheet'[Week])
)
VAR Missing = SELECTCOLUMNS( EXCEPT( AllCombinations, Submitted ), "Emp_ID", 'LogSheet'[Emp_ID] )
RETURN COUNTROWS( DISTINCT( Missing ) )

[HassanAshas]

Hi @johnt75 
This worked like a charm! Thank you soo much! 
I probably need to understand however that how is this working lol 😛 
but I will work on it. 

You used a Cross join however. That would badly affect the performance though, right? (especially in a larger dataset) 

[johnt75]

The CROSSJOIN generates all possible combinations of employee and week, and that then allows comparison with the actual combinations where a log sheet was submitted. I think you would need a very large number of employees and a lot of years before you run into serious performance issues - each employee will only generate 52 entries per year.

[johnt75]

I think you can adapt your existing code to iterate across all selected weeks, whether that is just one week or multiple.

Missing Logsheets =
SUMX (
    VALUES ( 'LogSheet'[Week] ),
    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
)

[HassanAshas]

Hi @johnt75  
Thank you so much for the reply. I really appreciate you taking time out and providing the answer. 

I am not sure what's the problem but it is not working correctly. 

For example, when I select two weeks together, it shows me 4 employees (but instead, it should only show 3 employees because "Leo" submitted Logsheet in both the weeks) 

Similarly, if I select all three weeks, then it shows 6 employees, which is wrong because total number of employees are only 4