Count on distinct cell

[NVieira]

Hi,

I'm working with a dataset like the one below.
I would like to count the number of "found"/"not found" for distinct IDs and with Status Closed

So, for the example below the expected result would be found =2 and not found =1

How can i manage this in PowerBI desktop?

ID	Status	Reason
21300	CLOSED	found
21300	CLOSED	found
21300	CLOSED	found
21300	CLOSED	found
21301	CLOSED	found
21302	IN_PROGRESS
21303	CLOSED	not found
21304	IN_PROGRESS
21305	IN_PROGRESS

[v-piga-msft]

Hi @NVieira,

You could have a try with the formula below.

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table1'[ID] ),
    FILTER ( ALLEXCEPT ( Table1, Table1[Reason] ), 'Table1'[Status] = "CLOSED" )
)

Here is the result output.

Best  Regards,

Cherry

[Sean · Accepted solution]

@NVieira

How about these 2 Measures...

Found Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[ID] ),
    FILTER ( 'Table', 'Table'[Reason] = "Found" && 'Table'[Status] = "Closed" )
)

Not Found Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[ID] ),
    FILTER ( 'Table', 'Table'[Reason] = "Not Found" && 'Table'[Status] = "Closed" )
)