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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
SOS4
Frequent Visitor

Difference between dates in 2 different tables based on a third column

Hi, I was hoping somone could assist me with an issue I'm having.
I want to track how quickly people are actioning and subsequently closing work 'tickets'
I want to calculate the difference between two dates in two seperate tables but there will be two difference calculations depending on the contents of a third and fouth column.
The two tables have a relationship set up
For Example: (Table 2 Date entered) - (Table 1 LCLEnterDate) when Table 1 WorkflowStateName is Open and Table 2 Event Type is On Demand. Result = Response Time. This should be a duration in hours and minutes
(Table 2 Date entered) - (Table 1 LCLEnterDate) when WorkflowStateName is Closed Table 2 Event Type is On Demand.= Total Time

 

Alternatively, Is it possible to spearate the data out into 4 columns like this:

WorkflowStateNameLCLEnterDateWorkflowStateNameLCLEnterDate
Open2022-05-04T21:41:04.9400000Closed2020-11-28T17:27:49.1230000
Open2021-06-08T15:26:25.5530000Closed2021-06-08T15:32:28.2530000
Open2021-11-22T02:01:23.2900000Closed2020-11-28T17:27:49.1230000
Open2021-11-23T02:01:59.7900000Closed2021-06-08T15:32:28.2530000
Open2021-06-05T21:03:28.7100000Closed2020-12-08T12:20:36.6500000

 


This is a sample of my data. I'd really appreciate any assistance or thoughts on how I should proceed
Table 1:

WorkflowNameWorkflowStateNameLCLEnterDate
On Demand Maintenance WorkOpen2022-05-04T21:41:04.9400000
On Demand Maintenance WorkSFU Review2022-05-04T21:57:18.3830000
On Demand Maintenance WorkClosed2022-05-05T13:36:06.1930000
General WorkOpen2021-06-08T15:26:25.5530000
General WorkClosed2021-06-08T15:32:28.2530000
On Demand Maintenance WorkOpen2020-11-18T10:52:44.2730000
On Demand Maintenance WorkOpen2020-11-11T09:31:04.2870000
On Demand Maintenance WorkClosed2020-11-12T15:44:35.4100000
General WorkOpen2021-08-09T01:04:58.1730000
General WorkClosed2021-08-09T01:09:14.9100000
General WorkOpen2022-04-19T14:02:47.9430000
General WorkClosed2022-04-19T14:04:00.9970000
On Demand Maintenance WorkOpen2022-04-04T22:14:08.8030000
On Demand Maintenance WorkClosed2022-04-04T22:16:13.4700000
General WorkOpen2022-06-05T02:54:25.1000000
General WorkClosed2022-06-05T02:58:03.0430000

 

 

Table 2:

M_EHRTNameAssetIDEvent TypeDateEntered
Maintenance WorkC12984On Demand2021-08-25T07:21:38.5170000
Maintenance WorkC12984On Demand2021-08-25T07:21:38.5170000
Maintenance WorkC9650MEV_C9650_012021-10-18T01:03:40.1930000
General WorkC12860On Demand2021-10-21T10:04:03.9900000
Maintenance WorkC13336On Demand2021-04-27T08:29:59.2830000
General WorkC13120On Demand2021-03-17T17:46:57.5300000
General WorkC11529On Demand2021-05-26T23:38:40.1570000
2 REPLIES 2
lbendlin
Super User
Super User

Please explain how these tables are linked together.

SOS4
Frequent Visitor

Hi,

The field MemberID in Table 1 Workflow logs is joined to the field EHMemberID in Table 2: Work. Cardinality is many:many and cross filter is both directions.

 

I added a simple subtraction measure outside of the transform data section I believe the values are showing as days. eg. every value is 1, instead of 00:30:35

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.