Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
WorkHard
Helper V
Helper V

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.

2 REPLIES 2
amitchandak
Super User
Super User

@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

 

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.