cancel
Showing results for
Did you mean:
Frequent Visitor

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

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:

1 ACCEPTED SOLUTION
Community Support

Hi @bar1694

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.

3 REPLIES 3
Community Support

Hi @bar1694

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.

Resolver IV

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

Frequent Visitor

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!

Announcements

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!