Count every single ID if status is only green

[anoonymous]

Hello guys,

I am trying to count every single ID where status is only green.

Based on screenshot the solution should be: 3 (ID:1 + ID:2 + ID:2) Every other ID can't be count in since there is one status = red.

Can anybody help me ? 

[CNENFRNL]

For fun only, a showcase of powerful Excel worksheet formulas

[PaulDBrown · Accepted solution]

If you want to count the Unique Ids where the status is only green you can use the following:

Green IDs =
VAR _green =
    CALCULATETABLE (
        VALUES ( 'Status table'[ID] ),
        FILTER ( 'Status table', 'Status table'[Status] = "green" )
    ) //Returns a table of ID values whose status = "green"
VAR _NotGreen =
    CALCULATETABLE (
        VALUES ( 'Status table'[ID] ),
        FILTER ( ALL ( 'Status table' ), 'Status table'[Status] <> "green" )
    ) //Returns a table of ID Values whose status is not/also not green
RETURN
    COUNTROWS ( EXCEPT ( _green, _NotGreen ) )
//Except returns a table of IDs in which the ID values from the table returned by _NotGreen are excluded from the ID values returned by the _green table.

If, however, you want to count the rows for the IDs with only a green status you can use:

Green Id rows =
VAR _green =
    CALCULATETABLE (
        VALUES ( 'Status table'[ID] ),
        FILTER ( 'Status table', 'Status table'[Status] = "green" )
    )
VAR _NotGreen =
    CALCULATETABLE (
        VALUES ( 'Status table'[ID] ),
        FILTER ( ALL ( 'Status table' ), 'Status table'[Status] <> "green" )
    )
RETURN
    COUNTROWS ( CALCULATETABLE ( 'Status table', EXCEPT ( _green, _NotGreen ) ) )
//The same as the previous calculation, but counts the ROWS OF THE WHOLE TABLE (not only IDs) for IDs with only a status of = "green"