Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
StephenK
Resolver I
Resolver I

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!

1 ACCEPTED SOLUTION
StephenK
Resolver I
Resolver I

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)

View solution in original post

4 REPLIES 4
StephenK
Resolver I
Resolver I

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)

@StephenK , Kudos to you. 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
amitchandak
Super User
Super User

@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)

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.