Forum Discussion
DAX count unique rows is counting ALL rows?
Hey folks, I have an issue that I hope is an easy fix.
I have a little bit of DAX code that's misbehaving. Here's the code in question:
Total Courses in a Program = IF ( HASONEVALUE ( CP[Column1] ) , CALCULATE( COUNTROWS ( ALL ( (CP) ) ) ) )
and here's a table to demonstrate how my data is laid out in the (CP) table:
| Program Title | Program Grouping | Course Title |
| Example Power BI Program | Subgroup 1 | Introduction to DAX |
| Example Power BI Program | Subgroup 1 | DAX 101 |
| Example Power BI Program | Subgroup 1 | DAX 102 |
Example Power BI Program | Subgroup 1 | DAX 201 |
| Example Power BI Program | Subgroup 1 | DAX 202 |
| Example Power BI Program | Subgroup 1 | DAX 301 |
| Example Power BI Program | Subgroup 1 | DAX 302 |
| Example Power BI Program | Subgroup 2 | Learning to Code |
| Example Power BI Program | Subgroup 2 | Learning to Debug |
So for my DAX code, what it SHOULD be doing is counting the total # of courses (Column 3 of my table directly above) within a Program (Column 1 of my table directly above). So the formula should look at this chunk of the data set and realize that there are 9 total courses within the Example Power BI Program.
Instead, it's returning the total # of rows in the dataset (836) instead of the # of courses in the Program (9). I think it's the ALL function within the formula, but I thought I needed that there to be sure to count ALL of the courses within the program. If I remove it then the results are less wrong, but still not correct.
For context as to why I needed the ALL piece there, I need Power BI to set the # of courses as an immutable, unchanging number. That way, if a user in my dataset is missing courses, Power BI will still use the correct # of courses in a Program rather than the # of courses it finds for a user (meaning a user may only complete 2 of those 9 courses, and without the ALL function, it returns 2 for the # of courses in a program because that's what the user has completed, and that # is wrong).
This was working before, but isn't now, and I'm not sure what I did to break it. Can anyone give me insight into what I can do to accomplish my desired outcome without having to worry about it being incorrect in the future? Any insight/advice is greatly appreciated! 🙂
12 Replies
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
Hi,
Please try this:
Total Courses in a Program = IF ( HASONEVALUE ( CP[Column1] ), CALCULATE ( COUNTROWS ( CP ), ALL ( CP ) ) )Best Regards,
Giotto Zhi
Hello v-gizhi-msft,
Thank you for your suggestion! I tried it and unfortunately it returns the total number of rows within the sheet (836). The one bright side is that the # of 836 with your formula doesn't change, so that's at least half way perfect!
A question for anyone still willing to assist with this (my own ignorance with Power BI paired with the inability to find a resolution has to be a huge turn off, I know), would trying to do a calculation of some kind in a calculated column on the table itself be a more foolproof method to accomplish this? I just learned about calculated columns earlier this morning, and it sounds like figuring out a way to do this through a calculated column would allow me to reference that calculated column for my division formula but still give me an accurate # since it would calculate on the table and ignore slicers (in theory, again, brand new to calculated columns).
Whether anyone is able/willing to answer or not, I do want to take a moment to issue a sincere thank you to anyone and everyone who has taken time out of their day to attempt to help me with this. This community is great and I hope everyone knows that your contributions and assistance are genuinely appreciated and valued.
Hey Anonymous ,
you may consider creating sample data, that reflects your data model.
From my understanding, you need something that returns a table that contains at least two columns (program | course). Of course, you can also count the rows inside this table, COUNTROWS(<virtualtable>) eq 9. But you have to use this table to filter the table that contains the employee, the program, and the completed course. Maybe this table also has a column that represents the status of the course (in progress, failed, completed), then it's necessary to filter for completed courses only.
But without further knowledge of your data model it's really hard, and creating sample data is tedious, and will most likely never match your data model.
Regards,
Tom
