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.
Hi,
I'm trying to make a JOIN of sorts happen, but even though my tables have a column with the same name and type it is not recognized as a common join (see pictures). I've tried working around it but according to what I've read so far this should just work.
Some background information; I have one table, schedule, which contains the schedules of the employees. I also have one table containing the exact time they started their shift, this is timeregistration. These 2 are to be linked on EmployeeID and the datetime to which they are closest. As I'm still working on the first part I haven't woried about the closest datetime as of yet, but aditional feedback or suggestions would always be appreciated.
I have made this query work in SQL (my connection is to a Azure SQL server) and it looks like this:
SELECT tr.Employee_EmployeeID, tr.RawStartTime, p.StartDateTime, (DATEDIFF(MINUTE,tr.RawStartTime, p.StartDateTime)) as diff FROM bigkiki.timeregistration tr JOIN bigkiki.actual p --Join on EmployeeID ON tr.Employee_EmployeeID = p.Employee_EmployeeID WHERE 3600 >= DATEDIFF(SECOND, tr.RawStartTime, p.StartDateTime) -- Diffrence smaller then 1 hour AND -3600 <= DATEDIFF(SECOND, tr.RawStartTime, p.StartDateTime) -- Diffrence larger then -1 hour ORDER BY tr.RawStartTime;
Any help would be greatly appreciated.
With kind regards,
Martien
(edit: there is more data then the rows shown in the screenshots, so that is not forming I problem, I think)
Solved! Go to Solution.
Solved with this DAX query
date_diff = FILTER( ADDCOLUMNS( GENERATEALL( schedule; VAR schedule_id = schedule[EmployeeID] RETURN SELECTCOLUMNS( CALCULATETABLE( timeregistration; timeregistration[EmployeeID] = schedule_id ); "StartTime_timeregistration"; timeregistration[StartTime_actual] ) ); "diff"; DATEDIFF([StartTime_schedule];[StartTime_timeregistration];SECOND) ); 3600 >= [diff] && -3600 <= [diff] && NOT(ISBLANK([StartTime_timeregistration])) )
Solved with this DAX query
date_diff = FILTER( ADDCOLUMNS( GENERATEALL( schedule; VAR schedule_id = schedule[EmployeeID] RETURN SELECTCOLUMNS( CALCULATETABLE( timeregistration; timeregistration[EmployeeID] = schedule_id ); "StartTime_timeregistration"; timeregistration[StartTime_actual] ) ); "diff"; DATEDIFF([StartTime_schedule];[StartTime_timeregistration];SECOND) ); 3600 >= [diff] && -3600 <= [diff] && NOT(ISBLANK([StartTime_timeregistration])) )
Works like a charm , Thanks for sharing.😉
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 |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |