Lookup unique key and if job time falls within the time frame then return a few values.

[Anonymous]

Hi there, hope i can get some helps from here.

In powerbi, i have two tables. 

AGV MISSION - Table 1

Job Time             Wayfinder       WayfinderJob

6/02/2023 6:31	4G780.06	4G780.06+Meal Send
6/02/2023 6:31	2G720.02	2G720.02+Meal Send
6/02/2023 6:33	4E300.04	4E300.04+Meal Send
6/02/2023 6:36	4G780.06	4G780.06+Meal Send
6/02/2023 6:37	2F490.02	2F490.02+Meal Send
6/02/2023 6:40	8F331.04	8F331.04+Meal Send
6/02/2023 6:40	9G351.03	9G351.03+Meal Send
6/02/2023 6:43	4F580.04	4F580.04+Meal Send
6/02/2023 6:44	8G421.03	8G421.03+Meal Send
6/02/2023 6:47	8E250.03	8E250.03+Meal Send
6/02/2023 6:51	8F331.04	8F331.04+Meal Send
6/02/2023 6:52	9E250.03	9E250.03+Meal Send
6/02/2023 6:53	9F301.04	9F301.04+Meal Send
6/02/2023 6:59	5F420.03	5F420.03+Meal Send

AGV SCHEDULE - Table 2

Wayfinder     Department Names       Schedule Time    Meals             WayfinderJob

2G720.02	2G East / 2F Central (129-136)	6:30:00 AM	Breakfast	2G720.02+Meal Send
4G780.06	4G Central/ 4G West	6:30:00 AM	Breakfast	4G780.06+Meal Send
4G780.06	4G North / 4G South	6:30:00 AM	Breakfast	4G780.06+Meal Send
9G351.03	9G West / 9G East (128-135)	6:45:00 AM	Breakfast	9G351.03+Meal Send
8G421.03	8G West / 8G East (128-135)	6:45:00 AM	Breakfast	8G421.03+Meal Send
5G530.03	5G West / 5G East (129-136)	7:00:00 AM	Breakfast	5G530.03+Meal Send
7G421.03	7G West / 7G East (128-135)	7:15:00 AM	Breakfast	7G421.03+Meal Send
6G461.03	6G North	7:15:00 AM	Breakfast	6G461.03+Meal Send
5G530.03	5g North	7:30:00 AM	Breakfast	5G530.03+Meal Send
6G461.03	6G West / 6G East (128-135)	7:30:00 AM	Breakfast	6G461.03+Meal Send
4G780.06	4G Central/ 4G West	8:30:00 AM	Breakfast	4G780.06+Meal Return
4G780.06	4G North / 4G South	8:30:00 AM	Breakfast	4G780.06+Meal Return
9G351.03	9G West / 9G East (128-135)	8:30:00 AM	Breakfast	9G351.03+Meal Return
2G720.02	2G East / 2F Central (129-136)	8:45:00 AM	Breakfast	2G720.02+Meal Return

If the wayfinder job is matched and job time is 15 mins before and after the Schedule time, return values of Meals, Department Names and Schedule Time.  I would like achieve it as per below table through Dax/Syntax. Not query editor. 

Job Time             Wayfinder         WayfinderJob           Meals           Department Names           Schedule Time

6/02/2023 6:31	4G780.06	4G780.06+Meal Send	Breakfast	4G Central/ 4G West	6:30:00 AM
6/02/2023 6:31	2G720.02	2G720.02+Meal Send	Breakfast	2G East / 2F Central (129-136)	6:30:00 AM
6/02/2023 6:33	4E300.04	4E300.04+Meal Send	Breakfast
6/02/2023 6:36	4G780.06	4G780.06+Meal Send	Breakfast	4G North / 4G South	6:30:00 AM
6/02/2023 6:37	2F490.02	2F490.02+Meal Send	Breakfast
6/02/2023 6:40	8F331.04	8F331.04+Meal Send	Breakfast
6/02/2023 6:40	9G351.03	9G351.03+Meal Send	Breakfast	9G West / 9G East (128-135)	6:45:00 AM
6/02/2023 6:43	4F580.04	4F580.04+Meal Send	Breakfast
6/02/2023 6:41	8G421.03	8G421.03+Meal Send	Breakfast	8G West / 8G East (128-135)	6:45:00 AM
6/02/2023 6:47	8E250.03	8E250.03+Meal Send	Breakfast
6/02/2023 6:51	8F331.04	8F331.04+Meal Send	Breakfast
6/02/2023 6:52	9E250.03	9E250.03+Meal Send	Breakfast
6/02/2023 6:53	9F301.04	9F301.04+Meal Send	Breakfast
6/02/2023 8:50	2G720.02	2G720.02+Meal Return	Breakfast	2G East / 2F Central (129-136)	8:45:00 AM

Many thanks

Cheers

kelvin

[v-shex-msft · Accepted solution]

HI @Anonymous,

You can try to use following calculated table formula to create a new table based on the AGV Mission table fields to append the second table fields based on conditions:

NewTable = 
Var offset=TIME ( 0, 15, 0 )
return
ADDCOLUMNS (
    'AGV MISSION',
    "Meals",
        CALCULATE (
          if(  MAX ( 'AGV SCHEDULE'[Meals] ) ="","Breakfast" ,MAX ( 'AGV SCHEDULE'[Meals] )),
            FILTER (
                'AGV SCHEDULE',
                [WayfinderJob] = EARLIER ( 'AGV MISSION'[WayfinderJob] )
                    && AND (
                        [Schedule Time]
                            >= TIMEVALUE ( EARLIER ( 'AGV MISSION'[Job Time] ) ) - offset,
                        [Schedule Time]
                            <= TIMEVALUE ( EARLIER ( 'AGV MISSION'[Job Time] ) )+ offset
                    )
            )
        ),
    "Department Names",
        CALCULATE (
            MAX ( 'AGV SCHEDULE'[Department Names] ),
            FILTER (
                'AGV SCHEDULE',
                [WayfinderJob] = EARLIER ( 'AGV MISSION'[WayfinderJob] )
                    && AND (
                        [Schedule Time]
                            >= TIMEVALUE ( EARLIER ( 'AGV MISSION'[Job Time] ) ) - offset,
                        [Schedule Time]
                            <= TIMEVALUE ( EARLIER ( 'AGV MISSION'[Job Time] ) )+ offset
                    )
            )
        ),
    "Schedule Time",
        CALCULATE (
          MAX ( 'AGV SCHEDULE'[Schedule Time] ),
            FILTER (
                'AGV SCHEDULE',
                [WayfinderJob] = EARLIER ( 'AGV MISSION'[WayfinderJob] )
                    && AND (
                        [Schedule Time]
                            >= TIMEVALUE ( EARLIER ( 'AGV MISSION'[Job Time] ) ) - offset,
                        [Schedule Time]
                            <= TIMEVALUE ( EARLIER ( 'AGV MISSION'[Job Time] ) ) + offset
                    )
            )
        )
)

Regards,

Xiaoxin Sheng

[Anonymous]

Perfect!. It did work. Thank you very much.