Forum Discussion
find duplication based on if condition
Greetings,
In my data I have IDs in a column that can be dublicated and status (open or closed) for these IDs. I need to create another column "called varify" to check if all cases belong to that IDs is closed or not, if at least one of the status is not closed, then all should be open.
Current table
Target table
from the target table, ID 100001 is still open since two (key 3 and 4 ) are open
Hi Me7sin ,
I made simple samples and you can check the results below:
_True = IF(MAX('Table'[Status])="Open",1,0) Varify = var _t = ADDCOLUMNS('Table',"Varify",IF(MAXX(FILTER(ALL('Table'),[ID]=EARLIER([ID])),[_True])=1,"Open","Closed")) RETURN MAXX(_t,[Varify])An attachment for your reference. Hope it helps!
Best regards,
Community Support Team_ Scott ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- amitchandak
Super User
Me7sin , No need of table, a new column can do
New column
var _t = countx(filter(Table, [ID] = earlier([ID]) ), [ID] )
var _c = countx(filter(Table, [ID] = earlier([ID]) && [Status] = "closed" ), [ID] )
return
if(_t =_c , "Closed", "Open")
In visual you can display the new column
Hi Me7sin ,
I made simple samples and you can check the results below:
_True = IF(MAX('Table'[Status])="Open",1,0) Varify = var _t = ADDCOLUMNS('Table',"Varify",IF(MAXX(FILTER(ALL('Table'),[ID]=EARLIER([ID])),[_True])=1,"Open","Closed")) RETURN MAXX(_t,[Varify])An attachment for your reference. Hope it helps!
Best regards,
Community Support Team_ Scott ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
