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!  