Forum Discussion

WorkHard's avatar
WorkHard
Icon for Helper V rankHelper V
5 years ago

Speed up DAX calculation when iterating through each row and a unique identifier

I'm trying to calculate the number of times the Start Date has changed for an event.
This works great but it already takes a good 5-7 seconds to calculate 5k rows and I'm anticipating much more rows soon.

 

-EventId--Start Date--Snapshot--Current Result-
Event11/15/20211/1/20210
Event13/15/20212/1/20211
Event13/17/20213/1/20212
Event13/17/20214/1/20212
Event23/1/20211/1/20210
Event24/4/20212/1/20211
Event24/1/20213/1/20212

 

c_# of start date changes = 
VAR CurrentEvent= datasource[EventId]
VAR CurrentSnapshot = datasource[Snapshot]
RETURN
CALCULATE(
    DISTINCTCOUNT(datasource[Start Date]),
        FILTER(
            ALL(datasource),
            datasource[EventId]=CurrentEvent
            && datasource[Snapshot] <= Snapshot
        )
)-1

 

I know there's a way to speed this up using an M calculation but I'm not familiar with them.

This table is already combined from multiple files (it uses a folder data source) so I'm not even sure I can use M in this scenario.

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    WorkHard , I think you are trying the right way. remove all from the column.  Or Try a  measure. Give code for that too

     

    c_# of start date changes = 
CALCULATE(
    DISTINCTCOUNT(datasource[Start Date]),
        FILTER(
            (datasource),
            datasource[EventId]=earlier(datasource[EventId])
            && datasource[Snapshot] <= earlier(datasource[Snapshot])
        )
)-1

or measure 

c_# of start date changes = 
CALCULATE(
    DISTINCTCOUNT(datasource[Start Date]),
        FILTER(
            ALL(datasource),
            datasource[EventId]=max(datasource[EventId])
            && datasource[Snapshot] <= max(datasource[Snapshot])
        )
)-1

     

     

    • WorkHard's avatar
      WorkHard
      Icon for Helper V rankHelper V
      5 years ago

      Thanks but removing the variables before the calculate method can only go one snapshot earlier. The "earlier" function can only detect 1 snapshot back this way. There are events that change the start date 5 times in the span of 5 snapshots.

       

      The measure will not work in my case because the results need to be static, no matter how the report is filtered.