Distinct Count on "active" status rows where there's no similar rows with "completed"

[onedayover]

Hi All

I have a table like this...

user	course	status
U5676	C2910	active
U5676	C2910	completed
U5676	C2938	active
U5676	C2930	active
U5676	C2985	active
U5676	C2915	active

How can I create a measure to calculate a distinct count on users in "active" courses, excluding rows where a user also has a row where the course has been marked as "completed"? I'm tring to exclude both rows in the count but just can't figure it out.

Expected result would be a distinct count of 4...

user	course	status
U5676	C2938	active
U5676	C2930	active
U5676	C2985	active
U5676	C2915	active

Many thanks

onedayover

[MahyarTF · Accepted solution]

Hi

I did create the below measure and put it in the Filter for the particular visual :

Status Check measure =

Var earlierStatus = calculate( max(Sheet77[status]), all(Sheet77), Sheet77[course] = SELECTEDVALUE(Sheet77[course]))

Var CurrStatus = SELECTEDVALUE(Sheet77[status])

return if (CurrStatus = earlierStatus && CurrStatus = "active", 1, 0)

Appreciate your Kudos and please mark it as a solution if it helps you