Identifying the first occurance of a value and restarting the count when there is a value inbetween

[MGinGermany]

Hello and thank you in advance!

I am trying to add a column in powerBI that would identify the first occurance of a value and then reidentify the same value when it comes back into the list. I think its easier explained with a data example:

Time	Value	Desired Column
05/14/2024 1:00:00	1	1
05/14/2024 2:00:00	1
05/14/2024 3:00:00	2
05/14/2024 4:00:00	3
05/14/2024 5:00:00	1	1
05/14/2024 6:00:00	1
05/14/2024 7:00:00	1
05/14/2024 8:00:00	2
05/14/2024 9:00:00	3
05/14/2024 10:00:00	1	1
05/14/2024 11:00:00	2
05/14/2024 12:00:00	3
05/14/2024 13:00:00	1	1
05/14/2024 14:00:00	1
05/14/2024 15:00:00	2
05/14/2024 16:00:00	1	1
05/14/2024 17:00:00	1
05/14/2024 18:00:00	4
05/14/2024 19:00:00	5
05/14/2024 20:00:00	6
05/14/2024 21:00:00	1	1
05/14/2024 22:00:00	1
05/14/2024 23:00:00	3
05/15/2024 00:00:00	1	1
05/15/2024 01:00:00	1
05/16/2024 02:00:00	2

The data is actually every 15 minutes, but I can remove duplicates up to the nearest hour already. Is there some way to add this column? I have tried to utilize COUNTROWS, EARLIER, DISTINCTCOUNT,  etc, but it either adds too many 1s or doesnt count after the first 1. 

It essentially needs to start looking for the next 1, and then ignore any that come consecutevely afterwards. 

Thanks again for the help!

[barritown · Accepted solution]

Hi @MGinGermany,

If you want to do it via DAX, here is an option:

In plain text:

Flag = 
VAR triggerValue = 1
VAR currentTime = [Time]
VAR previousTime = MAXX ( FILTER ( Data, [Time] < currentTime ), [Time] )
VAR previousValue = MINX ( FILTER ( Data, [Time] = previousTime ), [Value] )
RETURN IF ( previousValue <> triggerValue && [Value] = triggerValue, 1 )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[MGinGermany]

thanks, that worked perfectly!