March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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 |
Please explain how these tables are linked together.
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
21 | |
14 | |
11 | |
8 | |
5 |
User | Count |
---|---|
28 | |
21 | |
20 | |
13 | |
10 |