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:
So far, with the help of another user on here, I was able to get the "Missing" timesheets to show up using this measure:
Here is my end goal:
I don't see where I can upload my actual PowerBI file, but I can upload the mockup file if needed if someone could tell me how.
Thanks for any help you may be able to provide!
Solved! Go to Solution.
Hi @DJLight890 ,
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 ) )
I apologize if I'm not explaining it well. So as seen in the picture in my original post, I like that I am now able to see the employees that are missing, pending, or approved. I'm trying to get to where I can see the count of those that are "Missing", "Approved", or "Pending" as seen in this screenshot I found (In progress timesheets would be equivalent to my pending):
Hi @DJLight890 ,
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" ) )
@yingyinr 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?
@marceloVVR Thank you for your reply! I was able to get this to work too! I'm awaiting reply from the other person helping with this before I accept one of the replies as the solution. Thanks again!
Hi @DJLight890 ，
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.
@yingyinr 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:
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:
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!
Hi @DJLight890 ，
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" ) )
Check out a full recap of the month!
Check out the winners of the recent 'Can You Solve These?' community challenge!
Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.