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

Hi Anonymous ,

You can create a measure as below to get it:

Count of Missing = 
CALCULATE (
    DISTINCTCOUNT ( 'Timesheet Hours'[Employee Name] ),
    'Employee List'[Timesheet Required] = "Yes",
    FILTER (
        'Timesheet Hours',
        'Timesheet Hours'[Period Name]
            = SELECTEDVALUE ( 'Timesheet Hours'[Period Name] )
            && [Approval] = "Missing"
    )
)

Best Regards

Rena

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous  Wow, thank you so much! That looks exactly like what I'm looking for!

     

    I'm not sure what I'm doing wrong, but I copied everything exactly as you have it, including making sure my measures were in the same tables as yours. However, when I use a card for Count of Missing, I'm getting "Blank". Any thoughts?

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous ，

      According to your screen shot, it seems you didn't create any slicer with field [Period Name]... Then the condition "'Timesheet Hours'[Period Name] = SELECTEDVALUE ( 'Timesheet Hours'[Period Name] )" will not get the value, it cause the final value is blank. 

      Please create a slicer using the field 'Timesheet Hours'[Period Name] as below screen shot, then check if can get the correct value. Any comment or problem later, please feel free to let me know. Thank you.

      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!