Forum Discussion

StephenK's avatar
StephenK
Resolver I
5 years ago
Solved

DistinctCount where Count > Than X

Hey all,

 

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

 

TestIDStudentIDDateResultIn Period
113/1/20FailYes
218/4/20FailYes
321/1/19PassYes

 

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!

DAX
distinctcount
measure
SUMMARIZE
Summary
VAR
variable
variables
  • StephenK's avatar
    StephenK
    5 years ago

    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)

4 Replies

  • StephenK's avatar
    StephenK
    Resolver I
    5 years ago

    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's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      StephenK
      Resolver I
      5 years ago

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