Forum Discussion
Consecutive value being checked
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())
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())
6 Replies
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!
It depends. What the other filters? Certin values for other colums? Can you send sample file?
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 amitchandak
Can you please help me out?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())
