Function that take into account several rows

[JulienBds]

Hello everyone, may I request your help for a small problem I have on my PBI:

To make it simple, there are two columns: one with the control ID, and one that says active or inactive.

But there can be multiple rows with the same control ID that shows different output (active & inactive) (example below): 

I would like to identify in the PBI which control  ID has only lines that says “inactive”: if it is the case I will give it the value 0, otherwise (if a control need ID has at least one line “active”) I will give it the value 1 in a new column.
This value will be add to all the lines of the control need ID (in the example It will be 1 for every lines of the new column, even when it says inactive).

I hope it is understandable, many thanks in advance for your help.

Best,

Julien

[ribisht17 · Accepted solution]

@JulienBds 

Here is the solution,

Solution = if(CALCULATE(min('Table'[Active]),ALLEXCEPT('Table','Table'[Control ID]))="Active",1,0)

Regards,

Ritesh

[JulienBds]

It works indeed, many thanks ! 

Regards,

Julien