Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DISTINCTCOUNT only on a certain condition

Hello,   I need to distinctcount the IDs when the status is only equal to "Pushed" or "Already pushed" (if an ID has a different status, don't count it). Here is a sample table : ID Status...
Need Help
  • mahoneypat's avatar
    mahoneypat
    4 years ago

    Here is one measure expression that should work.

     

    Only Pushed =
    COUNTROWS(
        FILTER(
            VALUES( T3[ID] ),
            ISBLANK(
                CALCULATE( COUNT( T3[ID] ), NOT T3[Status] IN { "Pushed", "Already Pushed" } )
            )
        )
    )

     

    Pat

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

Hi,

Please try the below measure, and check the attached file.

 

 

expected result: =
VAR allid =
    VALUES ( 'Fact Assets'[ID] )
VAR idnotwant =
    CALCULATETABLE (
        VALUES ( 'Fact Assets'[ID] ),
        NOT ( 'Fact Assets'[Status] IN { "Pushed", "Already pushed" } )
    )
VAR onlyidwant =
    EXCEPT ( allid, idnotwant )
RETURN
    COUNTROWS ( onlyidwant )