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...
Need Help
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.