Direct Query Usage

[RaghavRamky]

I currently have a list of 10 ID's .

Each ID, may run into different status.

Now if the Status is open for a particular ID, then exclude entire id of that set from the count.

For E.g., below data, 101,103 will be excluded which contains atleast one open status.

id,status

101,closed

101,open

102,closed

103,open

103,closed

count=1

How to proceed with this in Dax Direct Query, Power BI ? 

Thanks in Advance.

[v-ljerr-msft · Accepted solution]

Hi @RaghavRamky,

Based on my test, the formula below should work in DirectQuery in your scenairo. 

count of id = 
CALCULATE (
    COUNT ( Table1[id] ),
    FILTER (
        Table1,
        NOT (
            CONTAINS ( FILTER ( Table1, Table1[status] = "open" ), Table1[id], Table1[id] )
        )
    )
)

Regards