calculated column to check if value in a column exist in another column

[Anonymous]

Hi everyone,

I'm trying to calculate the amount of time a car spent in its assigned area, and the time spent outside of its area. 
one of the problems is that there are areas that overlap and appear together.
I have a table that contains the car plate, the actual area he was at and the timestamp, the assigned area, and the time he spent between each row:

in this example, the assigned area is area 1, and I want that the calculation will be something like: sum(time_difference) if area column contains assigned_area column.
so the result should be in this example is:
time in assigned area : sum (rows 869-883) = 00:13:55 
time spent outside the area: sum (884-888) = 00:05:00,

in a table like this:

please help me! thank you 🙂

[v-jingzhang · Accepted solution]

Hi @Anonymous 

You can first add two columns as below. Change "Date only" column to Date type.  

Date only = DATEVALUE([Date])


Is in assigned area = IF(CONTAINSSTRING('Table'[area],'Table'[assigned_area]),1,0)

Then create a new table with below DAX. This will return you a table with summarized time in minute unit. 

Table 2 = 
SUMMARIZE (
    'Table',
    'Table'[Date only],
    'Table'[Plate],
    'Table'[assigned_area],
    "time in assigned area",
        CALCULATE ( SUM ( 'Table'[Time Difference] ), 'Table'[Is in assigned area] = 1 ) * 24 * 60,
    "time spent outside the area",
        CALCULATE ( SUM ( 'Table'[Time Difference] ), 'Table'[Is in assigned area] = 0 ) * 24 * 60
)

If you want it to return in "hh:mm:ss" format, try 

Table 2 = 
SUMMARIZE (
    'Table',
    'Table'[Date only],
    'Table'[Plate],
    'Table'[assigned_area],
    "time in assigned area",
        FORMAT (
            CALCULATE ( SUM ( 'Table'[Time Difference] ), 'Table'[Is in assigned area] = 1 )
                + TIME ( 0, 0, 0 ),
            "hh:mm:ss"
        ),
    "time spent outside the area",
        FORMAT (
            CALCULATE ( SUM ( 'Table'[Time Difference] ), 'Table'[Is in assigned area] = 0 )
                + TIME ( 0, 0, 0 ),
            "hh:mm:ss"
        )
)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[dhruvinushah]

Hi @Anonymous , 
Can you please share a sample dataset of your example above that can be copy pasted in MS Excel. Thanks

[Anonymous]

sure, 

Plate	area	Date	assigned_area	Time Difference (hh:mm:ss)	Time Difference
6583880	area 1	5/16/2022 10:17	area 1	00:01:00	0.000694444
6583880	area 1	5/16/2022 10:18	area 1	00:01:00	0.000694444
6583880	area 1	5/16/2022 10:19	area 1	00:01:00	0.000694444
6583880	area 1	5/16/2022 10:20	area 1	00:01:00	0.000694444
6583880	area 1	5/16/2022 10:21	area 1	00:01:00	0.000694444
6583880	area 1	5/16/2022 10:22	area 1	00:01:00	0.000694444
6583880	area 1	5/16/2022 10:23	area 1	00:01:00	0.000694444
6583880	area 1, area 4	5/16/2022 10:24	area 1	00:01:00	0.000694444
6583880	area 1, area 4	5/16/2022 10:25	area 1	00:01:01	0.000706019
6583880	area 1, area 4	5/16/2022 10:26	area 1	00:01:00	0.000694444
6583880	area 1, area 4	5/16/2022 10:26	area 1	00:00:11	0.000127315
6583880	area 1, area 4	5/16/2022 10:28	area 1	00:01:00	0.000694444
6583880	area 1, area 4	5/16/2022 10:48	area 1	00:00:43	0.000497685
6583880	area 1, area 4	5/16/2022 10:50	area 1	00:01:00	0.000694444
6583880	area 1, area 4	5/16/2022 10:51	area 1	00:01:00	0.000694444
6583880	area 4	5/16/2022 10:52	area 1	00:01:00	0.000694444
6583880	area 4	5/16/2022 10:53	area 1	00:01:00	0.000694444
6583880	area 4	5/16/2022 10:54	area 1	00:01:00	0.000694444
6583880	area 4	5/16/2022 10:55	area 1	00:01:00	0.000694444
6583880	area 4	5/16/2022 10:56	area 1	00:01:00	0.000694444

thank you very much!