Match Sample Datetime to production run?

[tomr]

Hey Everyone,

I have 2 tables of Data, the first which contains the start and end times for a production run as well as the target value for a particular measured variable of the product.

Second table represents samples taken from each of those production runs however is limited to just the datetime the sample was taken and the measured level of the variable. I want to match these samples to the production run number from the first table. 

Examples of the two tables I want to match below.

Any idea about how to go about it?

[Phil_Seamark · Accepted solution]

Hi @tomr

If I understand correctly, you want your sample table to carry the production run and target value.

If so, please add these two calculated columns.  I have attached a PBIX file.

Product Run = 
    MAXX(
        FILTER(
            'Table1',
                'Table1'[Start Time] < EARLIER('Table2'[Datetime Sample]) && 
                EARLIER('Table2'[Datetime Sample]) < 'Table1'[End Time])
    ,[Production Run])

and

Target Value = 
    MAXX(
        FILTER(
            'Table1',
                'Table1'[Start Time] < EARLIER('Table2'[Datetime Sample]) && 
                EARLIER('Table2'[Datetime Sample]) < 'Table1'[End Time])
    ,[Target Value])

[tomr]

Perfect, thanks for that @Phil_Seamark