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
Anonymous
Not applicable

how to create measure for employee time entry adherence

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:

 

20200409.png

 

Fact Time Entry

Employee KeyDate Key
16

 

Dimension Date

Date KeyDay of Week
1Sunday
2Monday
3Tuesday
4Wednesday
5Thursday
6Friday
7Saturday

 

Dimension Employee

Employee KeyLocation
1a
2a
3b
4b

 

Time Entry Rules

LocationDay of Week
a

Monday

aTuesday
aWednesday
aThursday
aFriday
bSunday
bMonday
bTuesday
bWednesday
bThursday

 

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?

1 ACCEPTED SOLUTION
bheepatel
Resolver IV
Resolver IV

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).

 

Capture1.JPG

 

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).

 

Capture2.JPG

 

3. I created another measure:

Actual (X) = COUNTROWS(FILTER(DimensionEmployee, CONTAINS(FactTimeEntry, FactTimeEntry[Employee Key], DimensionEmployee[Employee Key])))
 
When I choose any day of the week in the slicer, I get the expected X and Y values. I assume Z is pretty straightforward to calculate from here. Below are screenshots of what the values look like when I choose "Fri" & "Wed" on the slicer.
 
Capture3.JPG

 

Capture4.JPG

The only trick in this case is that you have to select what day of the week it is - hope this helps!

 

 

 

 

View solution in original post

2 REPLIES 2
v-gizhi-msft
Community Support
Community Support

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:

6.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto

bheepatel
Resolver IV
Resolver IV

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).

 

Capture1.JPG

 

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).

 

Capture2.JPG

 

3. I created another measure:

Actual (X) = COUNTROWS(FILTER(DimensionEmployee, CONTAINS(FactTimeEntry, FactTimeEntry[Employee Key], DimensionEmployee[Employee Key])))
 
When I choose any day of the week in the slicer, I get the expected X and Y values. I assume Z is pretty straightforward to calculate from here. Below are screenshots of what the values look like when I choose "Fri" & "Wed" on the slicer.
 
Capture3.JPG

 

Capture4.JPG

The only trick in this case is that you have to select what day of the week it is - hope this helps!

 

 

 

 

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.