Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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[Column...
Need Help
MartynRamsden's avatar
MartynRamsden
Icon for Solution Sage rankSolution Sage
6 years ago

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,
Martyn

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.