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

@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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

@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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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. 

 

HassanAshas_0-1685263777718.png

 

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. 

@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! 

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.