Count Measurement Help

[kl8818]

Hi - need help creating a measurement.

Here is a sample of my data:

Item ID	Course Title	User ID	Last Name	First Name	Completion Status	Completion Date
000001	Course A	12345	Doe	Allison	Complete	4/21/2023
000001	Course A	23456	Doe	John	Complete	4/21/2023
000001	Course A	34567	Doe	Mark	Complete	9/26/2022
000002	Course B	34567	Doe	Mark	Complete	9/26/2022
000002	Course B	34567	Doe	Mark	Complete	9/26/2022
000003	Course C	34567	Doe	Mark	Complete	9/26/2022
000004	Course D	34567	Doe	Mark	Complete	9/26/2022
000001	Course A	45678	Doe	Beth	Complete	9/26/2022
000001	Course A	56789	Doe	Erika	Complete	9/28/2022
000002	Course B	45678	Doe	Beth	Complete	10/3/2022
000003	Course C	45678	Doe	Beth	Complete	10/3/2022
000004	Course D	45678	Doe	Beth	Complete	10/3/2022

What I would like to do is have a count for each user ID IF they have a completion for EACH course. So only count Mark Doe if he has completed Course A, B, C, and D.

[sjoerdvn]

try something like this

Count Users Completed All =
VAR coursecount =  DISTINCTCOUNT(Table[Course], ALL())
RETURN COUNTROWS(FILTER(VALUES(Table[User ID]),
	CALCULATE(DISTINCTCOUNT(Table[Course])) = coursecount
)) 

[Mahesh0016]

@kl8818 I hope this helps you.

Count User ID =

CALCULATE(

    COUNT('Table A'[User ID]),

    ALLEXCEPT('Table A','Table A'[Course Title],'Table A'[Completion Status])

)

[kl8818]

Hi - thank you for your response. It is still not filtering correctly with that formula. It is counting every line not filtering if the user has completed Course A, B, C, D, and E. I have some users who have only completed Course A and B for example and I do not want to count them in this measurement.