Forum Discussion

123CaM321's avatar
123CaM321
Frequent Visitor
2 years ago
Solved

Return value based on time-stamp

I have tableA with date/time entries (Max 1 per second)
I tableB with date/time entries (less frequent, no relation to other table) and at each time-stamp [VALUE]

I'd like to return [VALUE] for each row in tableA

What is the most efficient way to do this?

 

I have this DAX which seems to work but takes a very long time to return, leading me to believe it is very inefficient

 

Value =
VAR CurrentDateTime = 'tableA'[Time]
RETURN
    MAXX ( FILTER ( tableB, tableB[Time] < CurrentDateTime ), tableB[VALUE] )

 

What is the best approach to this?

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi 123CaM321 ,

    Regarding your question, is your connection mode Direct Query? After using DAX to create a calculated column, the query is sent to the data source, the data source returns all the data, and finally filtered by DAX. This may be the reason for the poor performance. You mentioned that you plan to implement it in Power Query. Please make sure that the steps applied support query folding, which will reduce the amount of data returned and thus improve performance.

     

    Best Regards,
    Wenbin Zhou

4 Replies

  • 123CaM321's avatar
    123CaM321
    Frequent Visitor
    2 years ago

    Thanks

    Was more after a human response from someone with experience opposed to AI. I think I need to look into getting this done in power query as it's likely more efficient than DAX.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi 123CaM321 ,

    Regarding your question, is your connection mode Direct Query? After using DAX to create a calculated column, the query is sent to the data source, the data source returns all the data, and finally filtered by DAX. This may be the reason for the poor performance. You mentioned that you plan to implement it in Power Query. Please make sure that the steps applied support query folding, which will reduce the amount of data returned and thus improve performance.

     

    Best Regards,
    Wenbin Zhou

    • 123CaM321's avatar
      123CaM321
      Frequent Visitor
      2 years ago

      Thanks for the reply. I am using a query and importing. I'm not finished testing yet, but what I've done is, join the tables in power query then use the fill down transformation to fill in the gaps. This adds TableB[VALUE] to every time point in TableA. So far from what I've seen, it is far more efficient manipulating the data this way.

      I probably could have constructed a query that would have skipped the join, but this works. Will report back

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi 123CaM321 ,

    Is your question solved? If so, please mark your answer as a solution so that more people can see it.

     

    Best Regards,
    Wenbin Zhou