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 w...
  • 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" )
)

     

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