Forum Discussion
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). :smileyvery-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! :smileylol:
2 Replies
jthomsonSolution Sage
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
- Greg_Deckler
Community Champion
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?
