Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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- |
Event1 | 1/15/2021 | 1/1/2021 | 0 |
Event1 | 3/15/2021 | 2/1/2021 | 1 |
Event1 | 3/17/2021 | 3/1/2021 | 2 |
Event1 | 3/17/2021 | 4/1/2021 | 2 |
Event2 | 3/1/2021 | 1/1/2021 | 0 |
Event2 | 4/4/2021 | 2/1/2021 | 1 |
Event2 | 4/1/2021 | 3/1/2021 | 2 |
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.
@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
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.
User | Count |
---|---|
122 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
187 | |
94 | |
67 | |
63 | |
54 |