[SOLVED] Distinct Count Rows with filter on Same column with Multiple Rows

Distinct Count Rows with filter on Same column with Multiple Rows

I'm new to DAX and still trying to get the hang of the basics!

I'm trying to get a distinct count based on whether a filter matches multiple values on the same column.

eg. based on the following dataset

PersonId     Col1
1            -1
1            10
2            -1
3            100
4            100
5            -1
5            30
6            -1
6            100

I want a distinct count where a PersonId has a row with -1 AND another row that isn't 100 for Col1.

[Edit] There needs to be a minimum of two rows for each PersonId. One row with -1 and another which isn't 100.

So that would be PersonId 1 and PersonId 5. Total 2.

This is the Dax I've got so far and it's not giving me the correct results. Returning 4 instead of 2.

_Measure = 
    CALCULATE (
        DISTINCTCOUNT('Data'[PersonId]),
        Data[Col1] IN { -1, 10, 40, 60, 90 } 
    )

Solution

UPDATED

Measure = 
VAR x = FILTER( ADDCOLUMNS( VALUES('Table'[PersonId]), "myCount", CALCULATE(COUNTROWS('Table'))), [myCount] >=2)

VAR a = CALCULATETABLE(VALUES('Table'[PersonId]), 'Table'[Col1] = -1, x)
VAR b=  CALCULATETABLE(VALUES('Table'[PersonId]),  'Table'[Col1] = 100, x)
VAR c = EXCEPT(a,b)
RETURN  

COUNTROWS(c)