Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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())
Solved! Go to 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" &&
, "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" &&
, "Check",
blank())
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.
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!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |