Measure to calculate distinct parent ID based on child ID attributes

[kampyy]

Hi,
I have one Risk table with these columns.

Risk ID	Control ID	Control Status
1	1	Closed
1	2	Cancelled
1	3	Open
2	1	Closed
2	2	Closed
3	1	Open
3	2	Closed

I want to create a Measure to """"Count Total of Distinct Risk ID, where none of the 'Control Status' is 'Open'."""
In short, the Measure should return 1 (i.e. Only Risk ID 2 has NO  'Control Status' = 'Open'.


Thanks alot.

[amitchandak · Accepted solution]

@kampyy , Try a measure like

countx(filter(summarize(Table, Table[Risk ID], "_1", countrows(filter(Table, Table[Control Status] <> "Open")) , "_2", countrows(filter(Table, Table[Control Status] = "Open"))), not(isblank([_1])) && isblank([_2])), [Risk ID])

[kampyy]

Thank you so much amit.
I was stuck on this measure from long time.