Calculate Time Intersection to Correlate Sequences of Independent Events

0 votes

So, we are building a PowerBI reporting solution and I solved a problem and the business came up with a new reporting idea. Not sure of the best way to approach it as I know very little about PowerBI and the business seems to want quite complex reports.

We have two sequences of events from separate data sources. They both contain independent events occurring to vehicles. One describes what location a vehicle is within - the other describes incident events which have a reason code for the incident. The business wants to report on time spent in each location for each reason. Vehicles can change location totally independent of the incident events occurring - and events actually are DateTime and occur at random points throughout the day. Each type of event has a start time/end time and a vehicle ID.

Vehicle Location Events

+------------------+-----------+------------+-----------------+----------------+
| LocationDetailID | VehicleID | LocationID |  StartDateTime  |  EndDateTime   |
+------------------+-----------+------------+-----------------+----------------+
|                1 |         1 |          1 |        2012-1-1 |      2016-1-1  |
|                2 |         1 |          2 |        2016-1-1 |      2016-4-1  |
|                3 |         1 |          1 |        2016-4-1 |      2016-11-1 |
|                4 |         2 |          1 |        2011-1-1 |      2016-11-1 |
+------------------+-----------+------------+-----------------+----------------+

Vehicle Status Events

+---------+---------------+-------------+-----------+--------------+
| EventID | StartDateTime | EndDateTime | VehicleID | ReasonCodeID |
+---------+---------------+-------------+-----------+--------------+
|       1 | 2012-1-1      | 2013-1-1    |         1 |            1 |
|       2 | 2013-1-1      | 2015-1-1    |         1 |            3 |
|       3 | 2015-1-1      | 2016-5-1    |         1 |            4 |
|       4 | 2016-5-1      | 2016-11-1   |         1 |            2 |
|       5 | 2015-9-1      | 2016-2-1    |         2 |            1 |
+---------+---------------+-------------+-----------+--------------+

Is there anyway I can correlate the two streams together and calculate total time per Vehicle per ReasonCode per location? This would seem to require me to be able to relate the two events - so a change of location may occur part way through a given ReasonCode.

Calculation Example ReasonCodeID 4

  • VehicleID 1 is in location ID 1 from 2012-1-1 to 2016-1-1 and 2016-4-1 to 2016-11-1
  • VehicleID 1 is in location ID 2 from 2016-1-1 to 2016-4-1
  • VehcileID 1 has ReasonCodeID 4 from 2015-1-1 to 2016-5-1

Therefore first Period in location 1 intersects with 365 days of ReasonCodeID 4 (2015-1-1 to 2016-1-1). 2nd period in location 1 intersects with 30 days (2016-4-1 to 2016-5-1). In location 2 intersects with 91 days of ReasonCodeID 4(2016-1-1 to 2016-4-1

Desired output would be the below.

+-----------+--------------+------------+------------+
| VehicleID | ReasonCodeID | LocationID | Total Days |
+-----------+--------------+------------+------------+
|         1 |            1 |          1 |        366 |
|         1 |            3 |          1 |        730 |
|         1 |            4 |          1 |        395 |
|         1 |            4 |          2 |         91 |
|         1 |            2 |          1 |        184 |
|         2 |            1 |          1 |        154 |
+-----------+--------------+------------+------------+

Vehicles have related tables and I'm sure the business will want them grouped by vehicle class etc but if I can't really understand how to calculate the intersection points in this case. And that could give me the basis for the rest of reporting.

Oct 26, 2018 in Power BI by Shubham
• 13,190 points
36 views

1 answer to this question.

0 votes

I think this solution requires a CROSS JOIN implementation. The relationship between both tables is Many to Many which implies the creation of a third table that bridges LocationEvents and VehicleStatusEvents tables so I think specifying the relationship in the expression could be easier.

I use a CROSS JOIN between both tables, then filter the results only to get those rows which VehicleID columns are the same in both tables. I am also filtering the rows that VehicleStatusEvents range dates intersects LocationEvents range dates.

Once the filtering is done I am adding a column to calculate the count of days between each intersection. Finally, the measure sums up the days for each VehicleID, ReasonCodeID and LocationID.

In order to implement the CROSS JOIN you will have to rename the VehicleID, StartDateTimeand EndDateTime on any of both tables. It is necessary for avoiding ambigous column names errors.

I rename the columns as follows:

VehicleID : LocationVehicleID and StatusVehicleID 
StartDateTime : LocationStartDateTime and StatusStartDateTime 
EndDateTime : LocationEndDateTime and StatusEndDateTime 

After this you can use CROSSJOIN in the Total Days measure:

Total Days =
SUMX (
    FILTER (
        ADDCOLUMNS (
            FILTER (
                CROSSJOIN ( LocationEvents, VehicleStatusEvents ),
                LocationEvents[LocationVehicleID] = VehicleStatusEvents[StatusVehicleID]
                    && LocationEvents[LocationStartDateTime] <= VehicleStatusEvents[StatusEndDateTime]
                    && LocationEvents[LocationEndDateTime] >= VehicleStatusEvents[StatusStartDateTime]
            ),
            "CountOfDays", IF (
                [LocationStartDateTime] <= [StatusStartDateTime]
                    && [LocationEndDateTime] >= [StatusEndDateTime],
                DATEDIFF ( [StatusStartDateTime], [StatusEndDateTime], DAY ),
                IF (
                    [LocationStartDateTime] > [StatusStartDateTime]
                        && [LocationEndDateTime] >= [StatusEndDateTime],
                    DATEDIFF ( [LocationStartDateTime], [StatusEndDateTime], DAY ),
                    IF (
                        [LocationStartDateTime] <= [StatusStartDateTime]
                            && [LocationEndDateTime] <= [StatusEndDateTime],
                        DATEDIFF ( [StatusStartDateTime], [LocationEndDateTime], DAY ),
                        IF (
                            [LocationStartDateTime] >= [StatusStartDateTime]
                                && [LocationEndDateTime] <= [StatusEndDateTime],
                            DATEDIFF ( [LocationStartDateTime], [LocationEndDateTime], DAY ),
                            BLANK ()
                        )
                    )
                )
            )
        ),
        LocationEvents[LocationID] = [LocationID]
            && VehicleStatusEvents[ReasonCodeID] = [ReasonCodeID]
    ),
    [CountOfDays]
)

Then in Power BI you can build a matrix.

If you don't understand completely the measure expression, here is the T-SQL translation:

SELECT
    dt.VehicleID,
    dt.ReasonCodeID,
    dt.LocationID,
    SUM(dt.Diff) [Total Days]
FROM 
(
    SELECT
        CASE
            WHEN a.StartDateTime <= b.StartDateTime AND a.EndDateTime >= b.EndDateTime  -- Inside range
               THEN DATEDIFF(DAY, b.StartDateTime, b.EndDateTime)
            WHEN a.StartDateTime > b.StartDateTime AND a.EndDateTime >= b.EndDateTime  -- |-----|*****|....|
               THEN DATEDIFF(DAY, a.StartDateTime, b.EndDateTime)
            WHEN a.StartDateTime <= b.StartDateTime AND a.EndDateTime <= b.EndDateTime  -- |...|****|-----|
               THEN DATEDIFF(DAY, b.StartDateTime, a.EndDateTime)
            WHEN a.StartDateTime >= b.StartDateTime AND a.EndDateTime <= b.EndDateTime  -- |---|****|-----
               THEN DATEDIFF(DAY, a.StartDateTime, a.EndDateTime)
        END Diff,
        a.VehicleID,
        b.ReasonCodeID,
        a.LocationID --a.StartDateTime, a.EndDateTime, b.StartDateTime, b.EndDateTime
    FROM LocationEvents a
        CROSS JOIN VehicleStatusEvents b
    WHERE a.VehicleID = b.VehicleID
        AND 
        (
            (a.StartDateTime <= b.EndDateTime)
                AND (a.EndDateTime >= b.StartDateTime)
        )
) dt
GROUP BY dt.VehicleID,
         dt.ReasonCodeID,
         dt.LocationID

Note in T-SQL you could use an INNER JOIN operator too.

Do let me know if it helps.

answered Oct 26, 2018 by Upasana
• 8,430 points

Related Questions In Power BI

+1 vote
2 answers

403 error thrown by power bi report server when trying to get list of data sets

Hey @saranya, Try removing the header and ...READ MORE

answered Oct 29, 2018 in Power BI by Kalgi
• 37,320 points
155 views
0 votes
1 answer

Calculate Total number of Male & Female in the PowerBI report

To achieve what you are looking for, ...READ MORE

answered Feb 28 in Power BI by Upasana
• 8,430 points
189 views
0 votes
1 answer

How to create final table based on Joins of two tables in power BI?

Hi Nithin, It is easy and simple. 1. Go ...READ MORE

answered Mar 1 in Power BI by Cherukuri
• 17,870 points
75 views
0 votes
1 answer

Add Column Count_of_people across two tables to get the count of people from a city.

Hi Anitha, 1. Merge both tables, using City ...READ MORE

answered Mar 22 in Power BI by Cherukuri
• 17,870 points
18 views
0 votes
1 answer

Time Series Chart in Power BI

Your Date format should be handled by ...READ MORE

answered Nov 26, 2018 in Power BI by Upasana
• 8,430 points
660 views
+3 votes
2 answers

Combine tables in Power BI

You can also achieve this using a ...READ MORE

answered Oct 5, 2018 in Power BI by lina
• 8,100 points

edited Oct 11, 2018 by Kalgi 111 views
0 votes
1 answer

Power Bi Dax Table

You need to wrap the numbers in ...READ MORE

answered Oct 5, 2018 in Power BI by Kalgi
• 37,320 points
53 views
0 votes
2 answers

Power BI Dax Multiple IF AND Statements

HI I have Column Patient 12 13 14 15 18 IN Patient=12, 13 Out ...READ MORE

answered May 22 in Power BI by VNK
5,553 views
0 votes
1 answer

To expand all lists in a row of lists at the same time without recurring values

Can't promise simple, but I have something ...READ MORE

answered Feb 19 in Power BI by Upasana
• 8,430 points
81 views
0 votes
1 answer

To Calculate the Standard Deviation in Power BI

Suppose you've to obtain the Standard Deviation ...READ MORE

answered Nov 26, 2018 in Power BI by Upasana
• 8,430 points
1,067 views