Forum Discussion

tomr's avatar
tomr
New Member
7 years ago
Solved

Match Sample Datetime to production run?

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?

 

 

 

 

 

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    7 years ago

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

2 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    7 years ago

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