Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to Solution.
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 Chang
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
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 Chang
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
@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
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
72 | |
38 | |
31 | |
26 |
User | Count |
---|---|
97 | |
86 | |
43 | |
40 | |
35 |