Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ronrule
New Member

Comparing Hours Scheduled to Hours Worked in 2 Fact Tables

pbix in OneDrive: https://1drv.ms/u/s!Aj43wr7aCxS_1jxQtptl6nx5r_re?e=ThSWml

 

  • I have a Schedule Fact table that contains Date, EmployeeID, StartTimes and EndTimes.
  • I have a Timesheet Fact table that contains Date, EmployeeID, InTimes and OutTimes.
  • They share an Employee Dimension and Date Dimension, joined by EmployeeID and Date.

 

I would like to show if the hours were worked as scheduled. Also if they were late, absent, or on-time. 

 

I'm struggling understanding how to model or create measures that compare without running into circular references.

5 REPLIES 5
ryan_mayu
Super User
Super User

the file is restricted due to the sensitivity label





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks! Updated it to Public.

What's the expected output based on the sample data?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I would like to show if the hours were worked as scheduled. Also if they were late, absent, or on-time. 

@ronrule 

you can try something like below

Measure = 
VAR _start=max(Schedule[Start Time])
VAr _end=max(Schedule[End Time])
VAR _scanstart=max(Timesheet[ScanInTime])
VAR _scanend=max(Timesheet[ScanOutTime])
return if(ISBLANK(_start),blank(),if(_scanstart>_start,"late",if(ISBLANK(_scanstart),"absence","ontime")))

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.