Forum Discussion
Calculate filter based on multiple table variables
Hi IvensaMDH ,
According to my understanding, it seems that you want to create a new table with Ids that there are Time for Type 1 and Type 2 ,and then get the time duration.
If so ,please try:
New Table =
VAR _t =
SUMMARIZE (
'Table',
'Table'[Id],
"Type 1 Time", LOOKUPVALUE ( 'Table'[Time], 'Table'[Id], [Id], 'Table'[Type], 1 ),
"Type 2 Time", LOOKUPVALUE ( 'Table'[Time], 'Table'[Id], [Id], 'Table'[Type], 2 )
)
RETURN
ADDCOLUMNS (
FILTER ( _t, [Type 1 Time] <> BLANK () && [Type 2 Time] <> BLANK () ),
"Time Diff(Minutes)",
( [Type 2 Time] - [Type 1 Time] ) * 24 * 60
)
Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
Thank you! This is exactly what i was looking for and done in an elegant way.
We do, however, have one slight edge case;
Sometimes, an entity will produce several events of the same type, but with different time. Example:
| Id | Type | Time |
| 1 | 1 | 15-03-2022 10:00:00 |
| 1 | 2 | 15-03-2022 11:00:00 |
| 2 | 1 | 15-03-2022 10:00:00 |
| 3 | 1 | 15-03-2022 10:00:00 |
| 3 | 2 | 15-03-2022 12:00:00 |
| 4 | 2 | 15-03-2022 13:00:00 |
| 1 | 1 | 15-03-2022 17:00:00 |
| 1 | 2 | 15-03-2022 19:30:00 |
In the above example, the entity with [Id] = 1 has produced 2 "sets" of events (a set being an event of [type] = 1 followed by an event of [type] = 2). Because of this, i have changed the query to FIRSTNONBLANKVALUE instead of LOOKUPVALUE (otherwise there may be multiple values returned).
I do however wonder, if it would be possible to traverse above table, and collect "sets" of events, producing the following:
| Id | Type 1 Time | Type 2 Time |
| 1 | 15-03-2022 10:00:00 | 15-03-2022 11:00:00 |
| 3 | 15-03-2022 10:00:00 | 15-03-2022 12:00:00 |
| 1 | 15-03-2022 17:00:00 | 15-03-2022 19:30:00 |
If you have any suggestions i'd love to hear them as it would be a great help for us!
Br,
/Mathias