DISTINCTCOUNT only on a certain condition

[DM_EU]

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
1	Pushed
2	Pushed
2	To do

The result should be 1 because only one ID has the status exactly equal to "Pushed" or "Already pushed". But my current measure is wrong because it returns 2. My current measure is :

CALCULATE(DISTINCTCOUNT ( 'Fact Assets'[ID]), 'Fact Assets'[Status] IN { "Pushed", "Already pushed" })

How can I get this measure to return 1 ?

Thank you

Best regards

[Jihwan_Kim]

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 )

[mahoneypat · Accepted solution]

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