Forum Discussion
Difference between two rows with with multiple filters
I have the following table which has an Index, and a ServiceIndex (indexed against Name & Service):
| Index | ServiceIndex | Name | Service | EnterOrExit | Time |
| 0 | 1 | John | Wash | Exit | 10:00:00 |
| 1 | 2 | John | Wash | Enter | 10:30:00 |
| 2 | 3 | John | Wash | Exit | 11:00:00 |
| 3 | 1 | John | Cut | Exit | 10:15:00 |
| 4 | 2 | John | Cut | Enter | 10:36:22 |
| 5 | 3 | John | Cut | Exit | 10:38:11 |
| 6 | 4 | John | Cut | Enter | 10:40:44 |
| 7 | 5 | John | Cut | Exit | 10:50:22 |
| 8 | 1 | Mike | Wash | Exit | 10:00:00 |
| 9 | 2 | Mike | Wash | Enter | 10:25:22 |
| 10 | 3 | Mike | Wash | Exit | 10:28:11 |
| 11 | 4 | Mike | Wash | Enter | 10:33:21 |
| 12 | 5 | Mike | Wash | Exit | 10:38:22 |
| 13 | 1 | Mike | Cut | Exit | 10:05:08 |
| 14 | 1 | Arthur | Trim | Exit | 10:02:35 |
| 15 | 2 | Arthur | Trim | Enter | 10:09:06 |
| 16 | 3 | Arthur | Trim | Exit | 10:15:36 |
| 17 | 1 | Arthur | Cut | Exit | 10:23:25 |
| 18 | 2 | Arthur | Cut | Enter | 10:33:22 |
| 19 | 3 | Arthur | Cut | Exit | 10:40:01 |
| 20 | 4 | Arthur | Cut | Enter | 10:42:22 |
| 21 | 5 | Arthur | Cut | Exit | 10:44:00 |
| 22 | 6 | Arthur | Cut | Enter | 10:48:36 |
| 23 | 7 | Arthur | Cut | Exit | 11:02:02 |
| 24 | 8 | Arthur | Cut | Enter | 11:11:05 |
I want to add a calculated column, using DAX, to calculate the difference in time between the current ServiceIndex, and the previous one (When previous does not exist because ServiceIndex=1, then value should be null)
So my desired result for the above table sample would be:
| Index | ServiceIndex | Name | Service | EnterOrExit | Time | TimeDifference |
| 0 | 1 | John | Wash | Exit | 10:00:00 | |
| 1 | 2 | John | Wash | Enter | 10:30:00 | 00:30:00 |
| 2 | 3 | John | Wash | Exit | 11:00:00 | 00:30:00 |
| 3 | 1 | John | Cut | Exit | 10:15:00 | |
| 4 | 2 | John | Cut | Enter | 10:36:22 | 00:21:22 |
| 5 | 3 | John | Cut | Exit | 10:38:11 | 00:01:49 |
| 6 | 4 | John | Cut | Enter | 10:40:44 | 00:02:33 |
| 7 | 5 | John | Cut | Exit | 10:50:22 | 00:09:38 |
| 8 | 1 | Mike | Wash | Exit | 10:00:00 | |
| 9 | 2 | Mike | Wash | Enter | 10:25:22 | 00:25:22 |
| 10 | 3 | Mike | Wash | Exit | 10:28:11 | 00:02:49 |
| 11 | 4 | Mike | Wash | Enter | 10:33:21 | 00:05:10 |
| 12 | 5 | Mike | Wash | Exit | 10:38:22 | 00:05:01 |
| 13 | 1 | Mike | Cut | Exit | 10:05:08 | |
| 14 | 1 | Arthur | Trim | Exit | 10:02:35 | |
| 15 | 2 | Arthur | Trim | Enter | 10:09:06 | 00:06:31 |
| 16 | 3 | Arthur | Trim | Exit | 10:15:36 | 00:06:30 |
| 17 | 1 | Arthur | Cut | Exit | 10:23:25 | |
| 18 | 2 | Arthur | Cut | Enter | 10:33:22 | 00:09:57 |
| 19 | 3 | Arthur | Cut | Exit | 10:40:01 | 00:06:39 |
| 20 | 4 | Arthur | Cut | Enter | 10:42:22 | 00:02:21 |
| 21 | 5 | Arthur | Cut | Exit | 10:44:00 | 00:01:38 |
| 22 | 6 | Arthur | Cut | Enter | 10:48:36 | 00:04:36 |
| 23 | 7 | Arthur | Cut | Exit | 11:02:02 | 00:13:26 |
| 24 | 8 | Arthur | Cut | Enter | 11:11:05 | 00:09:03 |
Im quite new at Power BI & DEX, so any help is greatly appreciated, and I hope my question makes sense.
Is this possible?
So far I have only been able to do it against the Index column, which does not produce the desired results, changing the below to use the ServiceIndex gives me error A table of multiple values was supplied where a single value was expected. I understand why I am getting the error but unsure on what to change/add to get the desired result.
UpOrDownTime =
VAR NextIndex = Event[Index] + 1
RETURN
Event[Time]
- CALCULATE (
VALUES ( Event[Time] ),
FILTER ( ALL ( Event ), Event[Index] = NextIndex )
)Hi Anonymous ,
Try this DAX calculated Column:
Column 4 = VAR Index = 'Table'[Index] VAR Reference = 'Table'[Service] VAR Prevtime = CALCULATE ( FIRSTNONBLANK ( 'Table'[Time], TRUE () ), FILTER ( 'Table', 'Table'[Index] = Index - 1 && 'Table'[Service] = Reference ) ) RETURN IF ( ISBLANK ( Prevtime), blank(), 'Table'[Time] - Prevtime )Here is my output based on your screenshot:
Let me know if this works.
Thanks,
Tejaswi
2 Replies
Hi Anonymous ,
Try this DAX calculated Column:
Column 4 = VAR Index = 'Table'[Index] VAR Reference = 'Table'[Service] VAR Prevtime = CALCULATE ( FIRSTNONBLANK ( 'Table'[Time], TRUE () ), FILTER ( 'Table', 'Table'[Index] = Index - 1 && 'Table'[Service] = Reference ) ) RETURN IF ( ISBLANK ( Prevtime), blank(), 'Table'[Time] - Prevtime )Here is my output based on your screenshot:
Let me know if this works.
Thanks,
Tejaswi
Hi,
This calculated column formula works
=if(ISBLANK(CALCULATE(MAX(Data[Time]),FILTER(Data,Data[Name]=EARLIER(Data[Name])&&Data[Service]=EARLIER(Data[Service])&&Data[ServiceIndex]<EARLIER(Data[ServiceIndex])))),BLANK(),Data[Time]-CALCULATE(MAX(Data[Time]),FILTER(Data,Data[Name]=EARLIER(Data[Name])&&Data[Service]=EARLIER(Data[Service])&&Data[ServiceIndex]<EARLIER(Data[ServiceIndex]))))
Hope this helps.
