Forum Discussion
Find total duration based on virtual filtered table.
I have a difficult situation(to my limited DAX knowledge). Row Data tabel copied below.
I have a table like below. What I need is filter this tabel by maximum [Duration] (in this case Type = [Gri]). Then see other two lines( Type = [BL], [Cru] )[Start] and [End] falls within the [Start] and [Finish] of this item( Type =[Gri]). If the [Start] and [End] falls outside of item type = [Gri] then keep those one in the table otherwise exclude.
[Start] and [End] are times. Data is stored in a minute level granularity. I am putting type in the pivot table column. to get the [Start] and [End] I use MIN and MAX
Type Duration Start End
BL 6 13:13 13:18
Cru 5 13:00 13:04
Gri 18 13:13 13:30
So final table looks like below since Type [BL] falls within 13:13 and 13:30 time slot exclude that in the final result. I have explore using virtual tables since I only need the total duration of below two, which should be 23. Could you please assist me on this. Your help is much appriciated.
Type Duration Start End
Cru 5 13:00 13:04
Gri 18 13:13 13:30
| Time | Type | Area | SubArea | Group |
| 13:13 | BL | A1 | SA1 | G1 |
| 13:14 | BL | A1 | SA1 | G1 |
| 13:15 | BL | A1 | SA1 | G1 |
| 13:16 | BL | A1 | SA1 | G1 |
| 13:17 | BL | A1 | SA1 | G1 |
| 13:18 | BL | A1 | SA1 | G1 |
| 13:13 | Gri | A1 | SA1 | G1 |
| 13:14 | Gri | A1 | SA1 | G1 |
| 13:15 | Gri | A1 | SA1 | G1 |
| 13:16 | Gri | A1 | SA1 | G1 |
| 13:17 | Gri | A1 | SA1 | G1 |
| 13:18 | Gri | A1 | SA1 | G1 |
| 13:19 | Gri | A1 | SA1 | G1 |
| 13:20 | Gri | A1 | SA1 | G1 |
| 13:21 | Gri | A1 | SA1 | G1 |
| 13:22 | Gri | A1 | SA1 | G1 |
| 13:23 | Gri | A1 | SA1 | G1 |
| 13:24 | Gri | A1 | SA1 | G1 |
| 13:25 | Gri | A1 | SA1 | G1 |
| 13:26 | Gri | A1 | SA1 | G1 |
| 13:27 | Gri | A1 | SA1 | G1 |
| 13:28 | Gri | A1 | SA1 | G1 |
| 13:29 | Gri | A1 | SA1 | G1 |
| 13:30 | Gri | A1 | SA1 | G1 |
| 13:00 | Gri | A1 | SA1 | G1 |
| 13:01 | Cru | A1 | SA1 | G1 |
| 13:02 | Cru | A1 | SA1 | G1 |
| 13:03 | Cru | A1 | SA1 | G1 |
| 13:04 | Cru | A1 | SA1 | G1 |
| 13:19 | Cru | A1 | SA1 | G1 |
Hi Jimmy,
Seems like this could work for me.
I have excluded some data in the miniute level granuality for the sake of simplicity.
My actual data looks like below. I need duration to calculate when only Avil column is '0'.
This is just a 30min sample only involving only 3 equips only. I have about 150 other type of equips.
What I am trying here is to get the total duration of area where Avil is '0' based on the orginal explenation. I am using Time Brush to change the time in the report.
I can email my PBIX file if you want to have a look.
You have been a great help so far.
Time Equip Avil Area SubArea Group 13:00 BL 1 A1 SA1 G1 13:01 BL 1 A1 SA1 G1 13:02 BL 1 A1 SA1 G1 13:03 BL 1 A1 SA1 G1 13:04 BL 1 A1 SA1 G1 13:05 BL 1 A1 SA1 G1 13:06 BL 1 A1 SA1 G1 13:07 BL 1 A1 SA1 G1 13:08 BL 1 A1 SA1 G1 13:09 BL 1 A1 SA1 G1 13:10 BL 1 A1 SA1 G1 13:11 BL 1 A1 SA1 G1 13:12 BL 1 A1 SA1 G1 13:13 BL 0 A1 SA1 G1 13:14 BL 0 A1 SA1 G1 13:15 BL 0 A1 SA1 G1 13:16 BL 0 A1 SA1 G1 13:17 BL 0 A1 SA1 G1 13:18 BL 0 A1 SA1 G1 13:19 BL 1 A1 SA1 G1 13:20 BL 1 A1 SA1 G1 13:21 BL 1 A1 SA1 G1 13:22 BL 1 A1 SA1 G1 13:23 BL 1 A1 SA1 G1 13:24 BL 1 A1 SA1 G1 13:25 BL 1 A1 SA1 G1 13:26 BL 1 A1 SA1 G1 13:27 BL 1 A1 SA1 G1 13:28 BL 1 A1 SA1 G1 13:29 BL 1 A1 SA1 G1 13:30 BL 1 A1 SA1 G1 13:00 Gri 1 A1 SA1 G1 13:01 Gri 1 A1 SA1 G1 13:02 Gri 1 A1 SA1 G1 13:03 Gri 1 A1 SA1 G1 13:04 Gri 1 A1 SA1 G1 13:05 Gri 1 A1 SA1 G1 13:06 Gri 1 A1 SA1 G1 13:07 Gri 1 A1 SA1 G1 13:08 Gri 1 A1 SA1 G1 13:09 Gri 1 A1 SA1 G1 13:10 Gri 1 A1 SA1 G1 13:11 Gri 1 A1 SA1 G1 13:12 Gri 1 A1 SA1 G1 13:13 Gri 0 A1 SA1 G1 13:14 Gri 0 A1 SA1 G1 13:15 Gri 0 A1 SA1 G1 13:16 Gri 0 A1 SA1 G1 13:17 Gri 0 A1 SA1 G1 13:18 Gri 0 A1 SA1 G1 13:19 Gri 0 A1 SA1 G1 13:20 Gri 0 A1 SA1 G1 13:21 Gri 0 A1 SA1 G1 13:22 Gri 0 A1 SA1 G1 13:23 Gri 0 A1 SA1 G1 13:24 Gri 0 A1 SA1 G1 13:25 Gri 0 A1 SA1 G1 13:26 Gri 0 A1 SA1 G1 13:27 Gri 0 A1 SA1 G1 13:28 Gri 0 A1 SA1 G1 13:29 Gri 0 A1 SA1 G1 13:30 Gri 0 A1 SA1 G1 13:00 Cru 0 A1 SA1 G1 13:01 Cru 0 A1 SA1 G1 13:02 Cru 0 A1 SA1 G1 13:03 Cru 0 A1 SA1 G1 13:04 Cru 0 A1 SA1 G1 13:05 Cru 1 A1 SA1 G1 13:06 Cru 1 A1 SA1 G1 13:07 Cru 1 A1 SA1 G1 13:08 Cru 1 A1 SA1 G1 13:09 Cru 1 A1 SA1 G1 13:10 Cru 1 A1 SA1 G1 13:11 Cru 1 A1 SA1 G1 13:12 Cru 1 A1 SA1 G1 13:13 Cru 1 A1 SA1 G1 13:14 Cru 1 A1 SA1 G1 13:15 Cru 1 A1 SA1 G1 13:16 Cru 1 A1 SA1 G1 13:17 Cru 1 A1 SA1 G1 13:18 Cru 1 A1 SA1 G1 13:19 Cru 0 A1 SA1 G1 13:20 Cru 1 A1 SA1 G1 13:21 Cru 1 A1 SA1 G1 13:22 Cru 1 A1 SA1 G1 13:23 Cru 1 A1 SA1 G1 13:24 Cru 1 A1 SA1 G1 13:25 Cru 1 A1 SA1 G1 13:26 Cru 1 A1 SA1 G1 13:27 Cru 1 A1 SA1 G1 13:28 Cru 1 A1 SA1 G1 13:29 Cru 1 A1 SA1 G1 13:30 Cru 1 A1 SA1 G1 You just need to modify the dax of first table like below:
New Table = SUMMARIZE ( FILTER ( 'Table', 'Table'[Avil] = 0 ), 'Table'[Type], "Duration", CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Type] ) ), "Start", CALCULATE ( MIN ( 'Table'[Time] ), ALLEXCEPT ( 'Table', 'Table'[Type] ) ), "End", CALCULATE ( MAX ( 'Table'[Time] ), ALLEXCEPT ( 'Table', 'Table'[Type] ) ) )Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
You can create two tables using DAX below:
New Table = SUMMARIZE ( 'Table', 'Table'[Type], "Duration", CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Type] ) ), "Start", CALCULATE ( MIN ( 'Table'[Time] ), ALLEXCEPT ( 'Table', 'Table'[Type] ) ), "End", CALCULATE ( MAX ( 'Table'[Time] ), ALLEXCEPT ( 'Table', 'Table'[Type] ) ) )Result = VAR Gri_Start = CALCULATE(MIN('New Table'[Start]), FILTER('New Table', 'New Table'[Type] = "Gri")) VAR Gri_End = CALCULATE(MIN('New Table'[End]), FILTER('New Table', 'New Table'[Type] = "Gri")) RETURN FILTER('New Table', 'New Table'[Start] < Gri_Start || 'New Table'[End] > Gri_End)Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Jimmy,
Seems like this could work for me.
I have excluded some data in the miniute level granuality for the sake of simplicity.
My actual data looks like below. I need duration to calculate when only Avil column is '0'.
This is just a 30min sample only involving only 3 equips only. I have about 150 other type of equips.
What I am trying here is to get the total duration of area where Avil is '0' based on the orginal explenation. I am using Time Brush to change the time in the report.
I can email my PBIX file if you want to have a look.
You have been a great help so far.
Time Equip Avil Area SubArea Group 13:00 BL 1 A1 SA1 G1 13:01 BL 1 A1 SA1 G1 13:02 BL 1 A1 SA1 G1 13:03 BL 1 A1 SA1 G1 13:04 BL 1 A1 SA1 G1 13:05 BL 1 A1 SA1 G1 13:06 BL 1 A1 SA1 G1 13:07 BL 1 A1 SA1 G1 13:08 BL 1 A1 SA1 G1 13:09 BL 1 A1 SA1 G1 13:10 BL 1 A1 SA1 G1 13:11 BL 1 A1 SA1 G1 13:12 BL 1 A1 SA1 G1 13:13 BL 0 A1 SA1 G1 13:14 BL 0 A1 SA1 G1 13:15 BL 0 A1 SA1 G1 13:16 BL 0 A1 SA1 G1 13:17 BL 0 A1 SA1 G1 13:18 BL 0 A1 SA1 G1 13:19 BL 1 A1 SA1 G1 13:20 BL 1 A1 SA1 G1 13:21 BL 1 A1 SA1 G1 13:22 BL 1 A1 SA1 G1 13:23 BL 1 A1 SA1 G1 13:24 BL 1 A1 SA1 G1 13:25 BL 1 A1 SA1 G1 13:26 BL 1 A1 SA1 G1 13:27 BL 1 A1 SA1 G1 13:28 BL 1 A1 SA1 G1 13:29 BL 1 A1 SA1 G1 13:30 BL 1 A1 SA1 G1 13:00 Gri 1 A1 SA1 G1 13:01 Gri 1 A1 SA1 G1 13:02 Gri 1 A1 SA1 G1 13:03 Gri 1 A1 SA1 G1 13:04 Gri 1 A1 SA1 G1 13:05 Gri 1 A1 SA1 G1 13:06 Gri 1 A1 SA1 G1 13:07 Gri 1 A1 SA1 G1 13:08 Gri 1 A1 SA1 G1 13:09 Gri 1 A1 SA1 G1 13:10 Gri 1 A1 SA1 G1 13:11 Gri 1 A1 SA1 G1 13:12 Gri 1 A1 SA1 G1 13:13 Gri 0 A1 SA1 G1 13:14 Gri 0 A1 SA1 G1 13:15 Gri 0 A1 SA1 G1 13:16 Gri 0 A1 SA1 G1 13:17 Gri 0 A1 SA1 G1 13:18 Gri 0 A1 SA1 G1 13:19 Gri 0 A1 SA1 G1 13:20 Gri 0 A1 SA1 G1 13:21 Gri 0 A1 SA1 G1 13:22 Gri 0 A1 SA1 G1 13:23 Gri 0 A1 SA1 G1 13:24 Gri 0 A1 SA1 G1 13:25 Gri 0 A1 SA1 G1 13:26 Gri 0 A1 SA1 G1 13:27 Gri 0 A1 SA1 G1 13:28 Gri 0 A1 SA1 G1 13:29 Gri 0 A1 SA1 G1 13:30 Gri 0 A1 SA1 G1 13:00 Cru 0 A1 SA1 G1 13:01 Cru 0 A1 SA1 G1 13:02 Cru 0 A1 SA1 G1 13:03 Cru 0 A1 SA1 G1 13:04 Cru 0 A1 SA1 G1 13:05 Cru 1 A1 SA1 G1 13:06 Cru 1 A1 SA1 G1 13:07 Cru 1 A1 SA1 G1 13:08 Cru 1 A1 SA1 G1 13:09 Cru 1 A1 SA1 G1 13:10 Cru 1 A1 SA1 G1 13:11 Cru 1 A1 SA1 G1 13:12 Cru 1 A1 SA1 G1 13:13 Cru 1 A1 SA1 G1 13:14 Cru 1 A1 SA1 G1 13:15 Cru 1 A1 SA1 G1 13:16 Cru 1 A1 SA1 G1 13:17 Cru 1 A1 SA1 G1 13:18 Cru 1 A1 SA1 G1 13:19 Cru 0 A1 SA1 G1 13:20 Cru 1 A1 SA1 G1 13:21 Cru 1 A1 SA1 G1 13:22 Cru 1 A1 SA1 G1 13:23 Cru 1 A1 SA1 G1 13:24 Cru 1 A1 SA1 G1 13:25 Cru 1 A1 SA1 G1 13:26 Cru 1 A1 SA1 G1 13:27 Cru 1 A1 SA1 G1 13:28 Cru 1 A1 SA1 G1 13:29 Cru 1 A1 SA1 G1 13:30 Cru 1 A1 SA1 G1 You just need to modify the dax of first table like below:
New Table = SUMMARIZE ( FILTER ( 'Table', 'Table'[Avil] = 0 ), 'Table'[Type], "Duration", CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Type] ) ), "Start", CALCULATE ( MIN ( 'Table'[Time] ), ALLEXCEPT ( 'Table', 'Table'[Type] ) ), "End", CALCULATE ( MAX ( 'Table'[Time] ), ALLEXCEPT ( 'Table', 'Table'[Type] ) ) )Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.