Forum Discussion

AI14's avatar
AI14
Icon for Helper III rankHelper III
3 years ago
Solved

measure to count dulicates

Hi

 

i want to count the total finished however there areID's that are duplicate. how do i count filter and only dount unique values of another?

 

i have a measure with the following formula:

= countx(filter(Table,Table[Status]="Finished"),Table[Status])

 

example: 

IDStatus
A12FINISHED
C67FINISHED
D54STARTED
C12FINISHED
A12FINISHED
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi AI14 

    please try

    =
    CALCULATE ( DISTINCTCOUNT ( Table[ID] ), Table[Status] = "Finished" )

  • AI14's avatar
    AI14
    3 years ago

    Thanks, worked.

     

    what if i wanted to add 2 filters

     

    i tried the below but didnt count, just showed blank

     

    CALCULATE ( DISTINCTCOUNT ( Table[ID] ), Table[Status] = "Finished"  && Table[Status] = "Started" )

4 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi AI14 

    please try

    =
    CALCULATE ( DISTINCTCOUNT ( Table[ID] ), Table[Status] = "Finished" )

    • AI14's avatar
      AI14
      Icon for Helper III rankHelper III
      3 years ago

      Thanks, worked.

       

      what if i wanted to add 2 filters

       

      i tried the below but didnt count, just showed blank

       

      CALCULATE ( DISTINCTCOUNT ( Table[ID] ), Table[Status] = "Finished"  && Table[Status] = "Started" )

      • FreemanZ's avatar
        FreemanZ
        Icon for Super User rankSuper User
        3 years ago

        try replacing && with ||

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi AI14 

    try to create a measure like this:

    count = 
    VAR _table =
    ADDCOLUMNS(
        VALUES(TableName[ID]),
       "Status",
       CALCULATE(MAX(TableName[Status]))
    )
    RETURN
    COUNTROWS(
        FILTER(_table, TableName[Status] = "Finished")
    )