Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 ) )
Solved! Go to Solution.
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.
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
User | Count |
---|---|
73 | |
70 | |
38 | |
24 | |
23 |
User | Count |
---|---|
96 | |
93 | |
50 | |
41 | |
40 |