Forum Discussion

AGo's avatar
AGo
Icon for Post Patron rankPost Patron
3 years ago

Dax iterate dates comparison

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

1 Reply

  • The general guidance is to try and reduce cardinality as soon as possible.This might be a option in your scenario. Please provide some sample data (ideally a lot but whatever you can do)  and please explain the comparison logic again.