Count of change of values from x to y

[RalphO]

Hey!

I've got the following data: 

time	state
2018-07-27 03:35:33.0000000	4
2018-07-27 03:35:35.0000000	4
2018-07-27 03:35:37.0000000	4
2018-07-27 03:35:39.0000000	4
2018-07-27 03:35:41.0000000	5
2018-07-27 03:35:43.0000000	5
2018-07-27 03:35:45.0000000	4
2018-07-27 03:35:47.0000000	4
2018-07-27 03:35:49.0000000	4
2018-07-27 03:35:51.0000000	6
2018-07-27 03:35:53.0000000	6
2018-07-27 03:35:55.0000000	6
2018-07-27 03:35:57.0000000	6
2018-07-27 03:35:59.0000000	4
2018-07-27 03:36:01.0000000	4
2018-07-27 03:36:03.0000000	6
2018-07-27 03:36:05.0000000	6

Now, i want to count how many times the state changes from 4 to 6. So the answer for this particular sample should be 2. NOTE: this has to be done with a measure, since I can not currently create calculated columns for my data set. 

Is this at all possible? And if yes, how do I do this??

Thanks in advance!

Ralph 

[v-frfei-msft · Accepted solution]

Hi @RalphO,

We can create the measures as below to achieve your goal.

rank = RANKX(ALLSELECTED(Table1),CALCULATE(MAX(Table1[time])),,ASC,Dense)

Measure = var pl = [rank]+1 
var sta = MAX(Table1[state])
return IF(sta=4 &&
CALCULATE(MAX(Table1[state]),FILTER(ALLSELECTED(Table1),[rank]=pl))=6,1,0)

result = SUMX(Table1,[Measure])

For more details, please check the pbix as attached.

Regards,

Frank

[RalphO]

That works, thank you!