Consecutive value being checked

[Jo5hua22]

Hi,

I would like your help to determine the below logic in the screenshot. Which is when there are consecutive values of "R" (more than 1 time) then the output should be displayed as "CHECK"

My current DAX code to determine CHECK/ NOT CHECK is given below

Output = 

var _seq = [S.No]
var _last = countx(filter(Table, Table[S.No] = _seq -1 && [KPI] = "R"),[S.No])
var _Next = countx(filter(Table, Table[S.No] = _seq +1 && [KPI] = "R"),[S.No])
return
Switch(True() ,
[KPI] = "R" && isblank(_last) && isblank(_next) , "No Check",
[KPI] = "R" && not(isblank(_last)) && isblank(_next) , "Check",
blank())

@amitchandak 

[Jo5hua22 · Accepted solution]

I found the answer

Output = 

var _seq = [S.No]
var _last = countx(filter(Table, Table[S.No] = _seq -1 && [KPI] = "R"),[S.No])
var _Next = countx(filter(Table, Table[S.No] = _seq +1 && [KPI] = "R"),[S.No])
return
Switch(True() ,
[KPI] = "R" && isblank(_last) && isblank(_next) , "No Check",
[KPI] = "R" &&

((not(isblank(_last)) && isblank(_next))||(not(isblank(_last)) && not(isblank(_next))))

, "Check",
blank())

[Jo5hua22]

@tamerj1 @amitchandak 
Can you please help me out?

[tamerj1]

It depends. What the other filters? Certin values for other colums? Can you send sample file?

[Jo5hua22]

Please find more detailed information. Basically if I know how to add filters in a Lookup inside an IF condition, I can solve it.

[tamerj1]

This calculated column should work:
Output =
IF (
Table[KPI] = "R",
IF (
LOOKUPVALUE ( Table[KPI], Table[S.No], Table[S.No] - 1 ) = "R",
"Check"
)
)

If ok, please consider marking my reply as accepted. Thank you!

[Jo5hua22]

Hi @tamerj1 

Thanks for the help. 

If I need more filters because I have a dataset with 2M rows, then where can I add the filter in your above DAX code?

Thanks in advance