Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help With Missing Timesheet Counts

Hello everyone,   I need some help with a Missing Timesheet report that I'm doing for my company. Basically, I have 2 tables that have a 1-to-many relationship: Employee List - table of employee ...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

    Please update the formula of measure "Missing count" as below:

    Missing Count = 
VAR _selections =
    ALLSELECTED ( 'Timesheet Hours'[Period Name] )
VAR _countofPeriod =
    COUNTROWS ( _selections )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Employee List'[Employee Name] ),
        'Employee List'[Timesheet Required] = "Yes"
    ) * _countofPeriod
        - CALCULATE (
            COUNT ( 'Timesheet Hours'[Period Name] ),
            'Employee List'[Timesheet Required] = "Yes",
            FILTER (
                ALL ( 'Timesheet Hours' ),
                'Timesheet Hours'[Period Name] IN _selections
            )
        )

     

     

    Best Regards

    Rena

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Anonymous  Thank you for your reply again. I'm sorry, in my screenshot, the "Period Name" slicer is hidden behind the dax dropdown, but I do have it setup. No matter which checkbox I use, I still see (Blank) Count of Missing. 

 

I copied and pasted your exact measure and even tried re-typing (in case of copy/paste error) exactly as you had it. Still seeing blank. I've tried reading more about some dax measures and I've tried variations of the following:

 

Pending Count = CALCULATE(COUNT('Timesheet Hours'[Employee Name]),
FILTER('Timesheet Hours',[Approval]="Pending"))

 

I was able to use the above to work for my Approved and Pending counts, but I realized that won't work for missing because the missing employees do not show up in 'Timesheet Hours[Employee Name]' because of the fact their timesheet is missing they won't ever appear in the table until it is submitted. 

 

So then I tried moving the [Approval] measure that I referenced in my very first post over to the 'Employee List' table (called [Approval2] and used this measure for missing counts:

 

Missing Count = CALCULATE(COUNT('Employee List'[Employee Name]),
FILTER('Employee List','Employee List'[Approval2]="Missing"))

 

This actually correctly counts the "Missing" employees when clicking on 1 single time period slicer, but if I try to use multiple slicers, the count is wrong (Ex: Jun22-28 has 2 missing, Jun 29-30 has 2 missing, but when I check both boxes, my missing count goes to 1 instead of the expected 4). 

 

 

Any thoughts? If you're able to provide any additional help I really appreciate it! Thank you for your previous time/help as well!

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ，

Please try to update the formula of measure "Missing Count" as below:

Missing Count =
VAR _selections =
    ALLSELECTED ( 'Timesheet Hours'[Period Name] )
RETURN
    CALCULATE (
        COUNT ( 'Timesheet Hours'[Period Name] ),
        'Employee List'[Timesheet Required] = "Yes",
        FILTER (
            'Timesheet Hours',
            'Timesheet Hours'[Period Name] IN _selections
                && [Approval] = "Missing"
        )
    )

Best Regards

Rena

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous  Unfortuntately I'm still getting (Blank)Missing Count showing up as seen in the screenshot below. I feel like we're so close. I tried a few things somewhat based on your previous measure, but haven't had any luck yet. I'll keep plugging away and reading up more on DAX to see what I can come up with.

     

    Thanks for your help so far and any additional help you may provide! 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous ,

      Please share some sample data in table Timesheet Hours and Employee List or your pbix file with us, then we can adjust the formula of related measures based on your scenario. Thank you.

      Best Regards

      Rena

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Anonymous  Yes, here is the link to the file. I haven't done this on here before, so let me know if you have any issues getting it.

         

        PowerBI File