Forum Discussion
DAX count unique rows is counting ALL rows?
Hey Tom, thanks for your reply! I tried both and tested them and I'm encountering the same issue. I'm including screenshots to show what I mean.
In the first screenshot, you can see that I've selected a Program, and in the Pie Chart it correctly summarizes how many courses are in that program (18) using either of the formulas you provided. So far so good!
But then if I choose an Employee so I can see how many of the required 18 courses she's completed, the Total Courses in Program formula updates to the total number of courses the employee has looked at/signed up for. So then the 18 goes to 6 because the Employee has only looked at 6 of the required 18 courses.
I have a table that's doing a very basic division calculation, but for that to be correct, the Total Courses in Program must remain static and correctly count the number of courses in a program, regardless of filters. But so far filters impact the Total Courses in Program calculation and it makes the division calculation I mentioned incorrect.
Hopefully that context may help identify where I'm doing something wrong.
Solution SageHi Anonymous
Try this:
Total Courses in a Program =
IF(
HASONEVALUE( CP[Course Title] ),
CALCULATE(
DISTINCTCOUNT( CP[Course Title] ),
ALLEXCEPT( CP, CP[Program Title] )
)
)
This will remove all filters except the one on Program Title.
Best regards,
Martyn
Thanks MartynRamsden I appreciate the help!
I HAVE to be doing something wrong, because all of the proposed solutions work at first, but the second I add a filter to anything the # count updates. Most recently, using your code, I created a Measure, entered your DAX formula, and saved it (though, in case it helps, putting it on a card to see if it was getting the correct # resulted in a (Blank)). It seemed like it returned the correct # of Courses, but the second I applied a filter it updated to the incorrect # again.
Would there be a higher likelihood of success if I just did a count of the Program title in Column A and disregarded Column C with the course titles in it? No 2 Programs are titled the same so there wouldn't be a concern about it counting separate programs twice.
TomMartens My apologies, I try to keep my threads short/concise and, since I'm very new to Power BI, wasn't sure what I needed to share without overwhelming people with too much unnecessary context. If I can't resolve the issue I'll try creating a test book, however the report I've created has so many measures and associations, along with a very large set of sensitive data, so I'm not sure I could genuinely recreate the environment I'm working in. Regardless, thank you very much for taking the time to write a formula at all, it's genuinely appreciated.