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

SOS4 — Thu, 14 Jul 2022 09:44:02 GMT

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:

WorkflowStateName	LCLEnterDate	WorkflowStateName	LCLEnterDate
Open	2022-05-04T21:41:04.9400000	Closed	2020-11-28T17:27:49.1230000
Open	2021-06-08T15:26:25.5530000	Closed	2021-06-08T15:32:28.2530000
Open	2021-11-22T02:01:23.2900000	Closed	2020-11-28T17:27:49.1230000
Open	2021-11-23T02:01:59.7900000	Closed	2021-06-08T15:32:28.2530000
Open	2021-06-05T21:03:28.7100000	Closed	2020-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:

WorkflowName	WorkflowStateName	LCLEnterDate
On Demand Maintenance Work	Open	2022-05-04T21:41:04.9400000
On Demand Maintenance Work	SFU Review	2022-05-04T21:57:18.3830000
On Demand Maintenance Work	Closed	2022-05-05T13:36:06.1930000
General Work	Open	2021-06-08T15:26:25.5530000
General Work	Closed	2021-06-08T15:32:28.2530000
On Demand Maintenance Work	Open	2020-11-18T10:52:44.2730000
On Demand Maintenance Work	Open	2020-11-11T09:31:04.2870000
On Demand Maintenance Work	Closed	2020-11-12T15:44:35.4100000
General Work	Open	2021-08-09T01:04:58.1730000
General Work	Closed	2021-08-09T01:09:14.9100000
General Work	Open	2022-04-19T14:02:47.9430000
General Work	Closed	2022-04-19T14:04:00.9970000
On Demand Maintenance Work	Open	2022-04-04T22:14:08.8030000
On Demand Maintenance Work	Closed	2022-04-04T22:16:13.4700000
General Work	Open	2022-06-05T02:54:25.1000000
General Work	Closed	2022-06-05T02:58:03.0430000

Table 2:

M_EHRTName	AssetID	Event Type	DateEntered
Maintenance Work	C12984	On Demand	2021-08-25T07:21:38.5170000
Maintenance Work	C12984	On Demand	2021-08-25T07:21:38.5170000
Maintenance Work	C9650	MEV_C9650_01	2021-10-18T01:03:40.1930000
General Work	C12860	On Demand	2021-10-21T10:04:03.9900000
Maintenance Work	C13336	On Demand	2021-04-27T08:29:59.2830000
General Work	C13120	On Demand	2021-03-17T17:46:57.5300000
General Work	C11529	On Demand	2021-05-26T23:38:40.1570000

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

lbendlin — Sat, 16 Jul 2022 23:14:42 GMT

Please explain how these tables are linked together.

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

SOS4 — Mon, 08 Aug 2022 16:00:23 GMT

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

topic Re: Difference between dates in 2 different tables based on a third column in DAX Commands and Tips

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

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

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