cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

COUNTIF in DAX going haywire?

Hey folks. Have been bashing my brain on a problem and tried multiple help posts' solutions to no avail.

I've have a fairly chunky set of data from a quiz (600k+ rows).

 

Each question has a [questionID] so we know which question is which.


Each user that takes the test has a [userID].

If they get a question right [answer-score] column is 1. If they get a question wrong [answer-score] column is 0.

 

I want to get:

 

a) a total of all questions answered (easy enough - a COUNT of all [answer-score]

b) a total of all questions answered wrong

c) a total of all questions answered right

 

It's the last two that are foxing me.

 

So far I have: created a new 'measure' thus:

Wrong = CALCULATE

(COUNT('public test_records'[answer_score]),'public test_records'[answer_score]=0)

 

That didn't work. I got, randomly... "33" as the answer. From 600k+ rows of data? Unlikely... even plain wrong (I counted more than that myself). Smiley Very Happy


So I tried: 


Right = CALCULATE(COUNT('test_records'[answer_score]),
FILTER('production test_records','production test_records'[answer_score]=1))

That gave me "50". Nice round number but again... "what the?"

Close to gnawing my keyboard. Grateful for any help. What am I missing? Oh for Excel and COUNTIF! Smiley LOL

2 REPLIES 2
Highlighted
Solution Sage
Solution Sage

Re: COUNTIF in DAX going haywire?

Don't think I've ever used count, I'd just use countrows here. Make a measure Answered similar to your first one for your all questions bit, then for each of your right/wrong things, make a measure Right= calculate([Answered],[answer-score]=1) and similar for wrong answers

Highlighted
Super User IV
Super User IV

Re: COUNTIF in DAX going haywire?

Sample data would assist tremendously. Are you sure there is no other context filters going on that would make the measure end up with those numbers?


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors