Forum Discussion
DAX count unique rows is counting ALL rows?
we have distinctcount to count distinct items. Do you want to count unique rows. means are there duplicate rows?
Hey there, thanks for the reply!
Using the example above, I want to count the rows where the value in column A is the same but the value in column C is unique. So in the table from my original post, you can see that Column A is titled Example Power BI Program and that is a unique title within my data set. But within that Program there are 9 courses. The course names can repeat throughout the data set (since the same course can belong to mulitple Programs), but I just want to know "How many unique courses are there within the Program titled Example Power BI Program?"
There are around 90 Programs and each one is comprised of a variety of courses.
Hopefully that made/makes sense and answers your question. Would using DISTINCTCOUNT in some way help alleviate the issue based on that clarification?
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
