cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bar1694
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:

bar1694_1-1653665882662.png

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:

bar1694_0-1653667650766.png


please help me! thank you 🙂

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
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)

 

vjingzhang_1-1653985641569.png

 

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
)

 

vjingzhang_0-1653984375761.png

 

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"
        )
)

vjingzhang_0-1653985533559.png

 

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

View solution in original post

3 REPLIES 3
v-jingzhang
Community Support
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)

 

vjingzhang_1-1653985641569.png

 

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
)

 

vjingzhang_0-1653984375761.png

 

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"
        )
)

vjingzhang_0-1653985533559.png

 

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

dhruvinushah
Resolver IV
Resolver IV

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

sure, 

 

PlateareaDateassigned_areaTime Difference (hh:mm:ss)Time Difference
6583880area 15/16/2022 10:17area 100:01:000.000694444
6583880area 15/16/2022 10:18area 100:01:000.000694444
6583880area 15/16/2022 10:19area 100:01:000.000694444
6583880area 15/16/2022 10:20area 100:01:000.000694444
6583880area 15/16/2022 10:21area 100:01:000.000694444
6583880area 15/16/2022 10:22area 100:01:000.000694444
6583880area 15/16/2022 10:23area 100:01:000.000694444
6583880area 1, area 45/16/2022 10:24area 100:01:000.000694444
6583880area 1, area 45/16/2022 10:25area 100:01:010.000706019
6583880area 1, area 45/16/2022 10:26area 100:01:000.000694444
6583880area 1, area 45/16/2022 10:26area 100:00:110.000127315
6583880area 1, area 45/16/2022 10:28area 100:01:000.000694444
6583880area 1, area 45/16/2022 10:48area 100:00:430.000497685
6583880area 1, area 45/16/2022 10:50area 100:01:000.000694444
6583880area 1, area 45/16/2022 10:51area 100:01:000.000694444
6583880area 45/16/2022 10:52area 100:01:000.000694444
6583880area 45/16/2022 10:53area 100:01:000.000694444
6583880area 45/16/2022 10:54area 100:01:000.000694444
6583880area 45/16/2022 10:55area 100:01:000.000694444
6583880area 45/16/2022 10:56area 100:01:000.000694444


thank you very much!

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

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

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

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

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors