Forum Discussion
DAX count unique rows is counting ALL rows?
Hey Anonymous
not sure what the outcome should look like, for this I created 2 measures:
distinct courses A =
IF(HASONEVALUE('CP'[Program Title])
, CALCULATE(
DISTINCTCOUNT('CP'[Course Title])
, ALL('CP'[Program Grouping])
, ALL(CP[Course Title])
)
)and
distinct courses B =
IF(HASONEVALUE('CP'[Program Title])
,
CALCULATE(
DISTINCTCOUNT('CP'[Course Title])
--, ALL('CP'[Program Grouping])
, ALL(CP[Course Title])
)
)
The following screenshot shows the difference:
Hopefully, this provides what you are looking for.
Regards,
Tom
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.
Hey Anonymous ,
your initial post did not mention what you are looking for as it does not mention
an employee
the number of not-completed courses within a program,
this means I spent some time to provide an answer to a question not asked. 😞
Please create a pbix that contains some sample data, but still reflects your data model, meaning also contains employee information and completed courses. Upload the pbix to onedrive or dropbox and share the link.
As a course can be inside multiple programs, does an employee has to complete the course multiple times if she/he wants to complete multiple programs?
Regards,
Tom
Hi 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,
MartynThanks 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.
