Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
HassanAshas
Helper V
Helper V

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

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

I have a dataset like this, 

 

Emp_IDNameProjectWeekStatus
1JohnA1Drafted
1JohnA2Missing
1JohnA3Missing
2LeoA1Submitted
2LeoB1Submitted
2LeoA2Submitted
2LeoB2Submitted
2LeoA3Missing
2LeoB3Missing
3AlissaC1Missing
3AlissaC2Missing
3AlissaC3Missing
4EmmaC1Missing
4EmmaB1Submitted
4EmmaC2Missing
4EmmaB2Missing
4EmmaC3Missing
4EmmaB3Drafted

 

  • 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

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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 ) )

View solution in original post

5 REPLIES 5
johnt75
Super User
Super User

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 ) )

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) 

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
Super User
Super User

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
)

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) 

 

HassanAshas_0-1685445288327.png

 

 

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

 

HassanAshas_1-1685445320791.png

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors