Hello,
I'm trying to optimize this surely wrong function, the calculation is heavy because of datetime fields (whith seconds precision) and because each of the two source tables has approx 1M rows:
FILTER(
GENERATE(
SELECTCOLUMNS(Stops,"aStart",Stops[Start],"aEnd",Stops[End],"aID",Stops[ID],"aMac",Stops[IDDevice])
,
SELECTCOLUMNS(Cycle,"bStart",Cycle[Start],"bEnd",Cycle[End],"bID",Cycle[ID])
)
,OR(AND([aStart]>=[bStart],[aStart]<=[bEnd]),AND([aEnd]>=[bStart],[aEnd]<=[bEnd]))
)
Stops table and Cycle table are yet related by IDDevice.
The overall target is to have a table of which malfunctionings in Stops table are affecting which cycle IDs.
I also tried doing that in M as an alternative:
Buffered=Table.Buffer(Cycle)
#"Added Custom" =
Table.AddColumn(Stops, "Custom",
(S)=> Table.SelectRows(Buffered,
(P)=> P[IDDevice]= S[IDDevice] and ((S[Start]>= P[Start] and S[Start]<=P[End])or(S[End]>= P[Start] and S[End]<=P[End]))
)
)
The problem is that both in DAX and M it's very slow, approx 1 row per second calculation and Table.Buffer seems not speeding up the query.
Can you please help me?
Thanks in advance
