Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to create a report about employee time entry adherence. My goal is that I have 100 % adherence, and the reason that I'm even creating this report is because I don't actually have 100 % adherence.
What makes my particular situation difficult is that whether or not an employee should be counted for time entry for the given day is a function of both the day of week and his/her location.
I am only interested in time entry from yesterday.
Here is my simplified data model and data:
Fact Time Entry
Employee Key | Date Key |
1 | 6 |
Dimension Date
Date Key | Day of Week |
1 | Sunday |
2 | Monday |
3 | Tuesday |
4 | Wednesday |
5 | Thursday |
6 | Friday |
7 | Saturday |
Dimension Employee
Employee Key | Location |
1 | a |
2 | a |
3 | b |
4 | b |
Time Entry Rules
Location | Day of Week |
a | Monday |
a | Tuesday |
a | Wednesday |
a | Thursday |
a | Friday |
b | Sunday |
b | Monday |
b | Tuesday |
b | Wednesday |
b | Thursday |
My 'Fact Time Entry' table only holds data from yesterday. I have severed the relationships between the 'Time Entry Rules' table and other tables to prevent a circular model.
I am using the following definitions:
Expected Number of Employees (y) = the number of employees who should have entered time yesterday (i.e., have a record in the 'Fact Time Entry' table)
Actual Number of Employees (x) = the number of employees who entered time yesterday (i.e., has a record in the 'Fact Time Entry' table)
Percentage of Time Entry (z) = the percentage of the expected number of employees who entered time yesterday
So, with the sample data given, my expected output is:
z = 1 / 2 = 50 %
obtained by:
-since 'Fact Time Entry'[Date Key] = '6' (i.e., Friday), then, via the 'Time Entry Rules' table, I expect only [Location] = 'a' employees to have entered data yesterday
so, y = 2 (i.e., employees 1 and 2)
but, x = 1 (i.e., employee 1)
How can I implement a DAX measure to return z?
Solved! Go to Solution.
Hi @Anonymous
Here is what I did:
1. I created the exact same 4 tables as you mentioned. However, I defined the relationships a bit differently (as seeen in the screenshot below).
2. I placed the Day of Week field from the DimensionDate table into a slicer. I also placed Employee Key from the DimensionEmployee table into a table. I then created the following measure:
Expected (Y) = COUNT(DimensionEmployee[Employee Key])
When I choose a particular day in the slicer, it will change the values in the table and it also gives the correct Y value as expected (see screenshot below).
3. I created another measure:
The only trick in this case is that you have to select what day of the week it is - hope this helps!
Hi,
Please try this measure:
z =
var WeekDay = CALCULATE(MAX('Dimension Date'[Day of Week]),FILTER('Dimension Date','Dimension Date'[Date Key]=MAX('Fact Time Entry'[Date Key])))
var Location = CALCULATE(MAX('Time Entry Rules'[Location]),FILTER('Time Entry Rules','Time Entry Rules'[Day of Week]=WeekDay))
var y = CALCULATE(DISTINCTCOUNT('Dimension Employee'[Employee Key]),FILTER('Dimension Employee','Dimension Employee'[Location]=Location))
var x = DISTINCTCOUNT('Fact Time Entry'[Employee Key])
return
x/y
The result shows:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto
Hi @Anonymous
Here is what I did:
1. I created the exact same 4 tables as you mentioned. However, I defined the relationships a bit differently (as seeen in the screenshot below).
2. I placed the Day of Week field from the DimensionDate table into a slicer. I also placed Employee Key from the DimensionEmployee table into a table. I then created the following measure:
Expected (Y) = COUNT(DimensionEmployee[Employee Key])
When I choose a particular day in the slicer, it will change the values in the table and it also gives the correct Y value as expected (see screenshot below).
3. I created another measure:
The only trick in this case is that you have to select what day of the week it is - hope this helps!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |