DistinctCount where Count > Than X

[StephenK]

Hey all,

I'm having trouble getting my measure to work correctly. I have a fact table that looks like:

TestID	StudentID	Date	Result	In Period
1	1	3/1/20	Fail	Yes
2	1	8/4/20	Fail	Yes
3	2	1/1/19	Pass	Yes

It's related to my User Dim table with cross filtering set to both.

I'm trying to get my measure to calculate a distinctcount of students who failed a test more than once in a 12 month period.

My current formula is:

Repeat Risk Users = 

var timesfailed = CALCULATE(COUNT('Fact Test Details'[TestID]),'Fact Test Details'[Result]="Fail",'Fact Test Details'[In Period]="Yes")

var repeatfail = CALCULATE(DISTINCTCOUNT('Fact Test Details'[StudentID]),FILTER('Fact Test Details',timesfailed>1))

Return
repeatfail

I've tried different variations of this, including trying a var summarize table, but nothing is giving me accurate numbers.

var timesfailed is giving the correct count of failures, but var repeatfail is giving an inaccurate result--way to high. No matter what i change the Filter timesfailed greater than to, the number is the same, unless I change it to =0.

Not sure what is wrong. Any suggestions would be awesome!

[StephenK · Accepted solution]

Looks like I got it working with this formula:

Times Failed = CALCULATE(COUNT('Fact Test Details'[TestID]),'Fact Test Details'[Result]="Fail",'Fact Test Details'[In Period]="Yes")


Repeat Failed =

Var FailCount = ADDCOLUMNS(SUMMARIZE('Fact Test Details','Fact Test Details'[StudentID]),"TimesFailed",[Times Failed])

Var _1 = FILTER(FailCount,[TimesFailed]>1)

Return COUNTROWS(_1)

[amitchandak]

@StephenK , Kudos to you. 

[amitchandak]

@StephenK , Create a measure like this and use with StudentID

countx(summarize(Table, Table[StudentID], "_1", calculate(distinctcount(Table[TestID]), Table[Result]="Fail" && Table[In Period]="Yes")),[_1]>1)

[StephenK]

@amitchandak  thanks for the response! This doesn't seem to work for me.

Repeat Failed =

COUNTX(SUMMARIZE('Fact Test Details','Fact Test Details'[StudentID],"TimesFailed",CALCULATE(DISTINCTCOUNT('Fact Test Details'[TestID]),FILTER('Fact Test Details','Fact Test Details'[Result]="Fail" && 'Fact Test Details'[In Period]="Yes"))),[TimesFailed]>1)

I get an error saying "The function COUNTX cannot work with values of type Boolean."