Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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:314G780.064G780.06+Meal Send
6/02/2023 6:312G720.022G720.02+Meal Send
6/02/2023 6:334E300.044E300.04+Meal Send
6/02/2023 6:364G780.064G780.06+Meal Send
6/02/2023 6:372F490.022F490.02+Meal Send
6/02/2023 6:408F331.048F331.04+Meal Send
6/02/2023 6:409G351.039G351.03+Meal Send
6/02/2023 6:434F580.044F580.04+Meal Send
6/02/2023 6:448G421.038G421.03+Meal Send
6/02/2023 6:478E250.038E250.03+Meal Send
6/02/2023 6:518F331.048F331.04+Meal Send
6/02/2023 6:529E250.039E250.03+Meal Send
6/02/2023 6:539F301.049F301.04+Meal Send
6/02/2023 6:595F420.035F420.03+Meal Send

 

AGV SCHEDULE - Table 2

Wayfinder     Department Names       Schedule Time    Meals             WayfinderJob

2G720.022G East / 2F Central (129-136)6:30:00 AMBreakfast2G720.02+Meal Send
4G780.064G Central/ 4G West6:30:00 AMBreakfast4G780.06+Meal Send
4G780.064G North / 4G South6:30:00 AMBreakfast4G780.06+Meal Send
9G351.039G West / 9G East (128-135)6:45:00 AMBreakfast9G351.03+Meal Send
8G421.038G West / 8G East (128-135)6:45:00 AMBreakfast8G421.03+Meal Send
5G530.035G West / 5G East (129-136)7:00:00 AMBreakfast5G530.03+Meal Send
7G421.037G West / 7G East (128-135)7:15:00 AMBreakfast7G421.03+Meal Send
6G461.036G North7:15:00 AMBreakfast6G461.03+Meal Send
5G530.035g North7:30:00 AMBreakfast5G530.03+Meal Send
6G461.036G West / 6G East (128-135)7:30:00 AMBreakfast6G461.03+Meal Send
4G780.064G Central/ 4G West8:30:00 AMBreakfast4G780.06+Meal Return
4G780.064G North / 4G South8:30:00 AMBreakfast4G780.06+Meal Return
9G351.039G West / 9G East (128-135)8:30:00 AMBreakfast9G351.03+Meal Return
2G720.022G East / 2F Central (129-136)8:45:00 AMBreakfast

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:314G780.064G780.06+Meal SendBreakfast4G Central/ 4G West6:30:00 AM
6/02/2023 6:312G720.022G720.02+Meal SendBreakfast2G East / 2F Central (129-136)6:30:00 AM
6/02/2023 6:334E300.044E300.04+Meal SendBreakfast  
6/02/2023 6:364G780.064G780.06+Meal SendBreakfast4G North / 4G South6:30:00 AM
6/02/2023 6:372F490.022F490.02+Meal SendBreakfast  
6/02/2023 6:408F331.048F331.04+Meal SendBreakfast  
6/02/2023 6:409G351.039G351.03+Meal SendBreakfast9G West / 9G East (128-135)6:45:00 AM
6/02/2023 6:434F580.044F580.04+Meal SendBreakfast  
6/02/2023 6:418G421.038G421.03+Meal SendBreakfast8G West / 8G East (128-135)6:45:00 AM
6/02/2023 6:478E250.038E250.03+Meal SendBreakfast  
6/02/2023 6:518F331.048F331.04+Meal SendBreakfast  
6/02/2023 6:529E250.039E250.03+Meal SendBreakfast  
6/02/2023 6:539F301.049F301.04+Meal SendBreakfast  
6/02/2023 8:502G720.022G720.02+Meal ReturnBreakfast2G East / 2F Central (129-136)

8:45:00 AM

 

Many thanks

Cheers

kelvin

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

1.PNGRegards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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

1.PNGRegards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors