how to create a power bi measure that filters out a code based on a id

I have a table that has multiple of the same codes and each code can have a different id to it as follows

Code | ID
45   | 2
45   | 5
45   | 8
45   | 7
21   | 1
21   | 9

i want to count the code that doesnt have and ID of 2 or 8 against it. However for example because the code of 45 has and id of 2 or 8 i want to ignore that code and only give me the code 21 which will be a count of 1

i have tried

Unsuccessful = CALCULATE(DISTINCTCOUNT('Table'[code]), FILTER('Table', NOT('Table'[ID] in {2,8})))

but the above is giving me a count of 2, when i want it to be 1. How do i ignore that code if it has 2 or 8 already present against it?

Solution

Your code is almost correct:

Unsuccessful = CALCULATE(DISTINCTCOUNT('Tables'[code]),FILTER('Tables', NOT(CALCULATETABLE(VALUES('Tables'[Code]), ('Tables'[ID] in {2,8})))))

The inner part returns the codes we want to exclude.

CALCULATETABLE(VALUES('Tables'[Code]), ('Tables'[ID] in {2,8})

First consider the inner part. CALCULATETABLE generate for us table from first argument based on filter in second). VALUES('Table'[Code]) -> produced distinct list of values from column, because of lineage it remember source (more: here https://www.sqlbi.com/?s=lineage). This part return code for which we find ID 2 or 8 (in this example Code = 45).

When we have a list of codes that we want to exclude, everything is simple. We just put FILTER inside CALCULATE. The first argument of FILTER is our base table, second - filter its self. Here we put table as a filter and because of Lineage, the engine "knows" relationship between data.