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
.
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 }
)