Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

First value of another table base on date

Hi,

I have 2 tables. Table 1 contains IDBoard and the date/time for each:

Table 2 contains production run with a start date and end date:

 

In table 2 I want to add a new column with the first IDBoard stamp after Start date of table 2. The expecting result should be:

Note: Table 1 contains around 30 millions rows!

How can I do that?
Thanks

 

 

 

 

 

Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    7 years ago

    Hi Anonymous ,

     

    You use this measure:

    First IDBoard =
VAR start1 =
    SELECTEDVALUE ( table2[start] )
VAR end1 =
    SELECTEDVALUE ( table2[end] )
RETURN
    CALCULATE (
        MIN ( table1[idBoard] ),
        table1[timeStamp] >= start1
            && table1[timeStamp] <= end1
    )

    Best Regards,

    Eads

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • vanessafvg's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Anonymous  can you provide the data in  text format?  (ie no pics)

  • v-eachen-msft's avatar
    v-eachen-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous ,

     

    You can create a measure in your second table.

    First IDBoard =
VAR start1 =
    SELECTEDVALUE ( table2[start] )
VAR end1 =
    SELECTEDVALUE ( table2[end] )
RETURN
    CALCULATE (
        MIN ( table1[idBoard] ),
        DATESBETWEEN ( table1[timeStamp], start1, end1 )
    )

    Then you can get result you want in a visual.

     

    Best Regards,

    Eads

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-eachen-msft ,

      This measure give me an error: duplicate dates dont't work with DATESBETWEEN function. 

      Any ideas?
      Thanks

      • v-eachen-msft's avatar
        v-eachen-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        Hi Anonymous ,

         

        You use this measure:

        First IDBoard =
VAR start1 =
    SELECTEDVALUE ( table2[start] )
VAR end1 =
    SELECTEDVALUE ( table2[end] )
RETURN
    CALCULATE (
        MIN ( table1[idBoard] ),
        table1[timeStamp] >= start1
            && table1[timeStamp] <= end1
    )

        Best Regards,

        Eads

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.