Forum Discussion

NVieira's avatar
NVieira
Regular Visitor
8 years ago
Solved

Count on distinct cell

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?

 

 

 

IDStatusReason
21300CLOSED found
21300CLOSED found
21300CLOSED found
21300CLOSED found
21301CLOSED found
21302IN_PROGRESS 
21303CLOSED not found
21304IN_PROGRESS 
21305IN_PROGRESS 
  • Sean's avatar
    Sean
    8 years ago

    NVieira

    How about these 2 Measures... :smileyhappy:

     

    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" )
)

     

2 Replies

  • Sean's avatar
    Sean
    Community Champion
    8 years ago

    NVieira

    How about these 2 Measures... :smileyhappy:

     

    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" )
)

     

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    8 years ago

    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