topic DistinctCount where Count > Than X in DAX Commands and Tips

DistinctCount where Count > Than X

StephenK — Fri, 22 Jan 2021 15:59:03 GMT

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!

Re: DistinctCount where Count > Than X

amitchandak — Fri, 22 Jan 2021 17:15:42 GMT

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)

Re: DistinctCount where Count > Than X

StephenK — Fri, 22 Jan 2021 17:56:35 GMT

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."

Re: DistinctCount where Count > Than X

StephenK — Fri, 22 Jan 2021 20:16:55 GMT

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)

Re: DistinctCount where Count > Than X

amitchandak — Mon, 25 Jan 2021 02:11:23 GMT

StephenK , Kudos to you.