Forum Discussion

StephenK's avatar
StephenK
Icon for Resolver I rankResolver 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:   TestID StudentID Date Result In Period 1 1 3/1/20 Fail Yes 2 1 8/4/20...
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)
amitchandak's avatar
amitchandak
Icon for Super User rankSuper 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
    Icon for Resolver I rankResolver 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."