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
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hey there,

Per your request, I scrubbed (hopefully) all of the sensitive data in my file and replaced it with dummy data. In order to speed things up I just made changes en masse, so some of the information might not line up or behave properly.

Also, be forewarned that my Fields view and Data Modeling are terribly messy, as I'm brand new to this program and haven't taken the time yet to go clean things up. As a result there are a lot of unused measures, and my data modeling is probably pretty bad.

 

To test how this is breaking, I suggest using Course Program 1 since it has a lot of courses in it, and then Employee Name 100 as the example of someone who only has 1 course done in Course Program 1 (filtering with the employee then updates the data which is what I'm trying to avoid).

 

Lastly, I've been through so many iterations of this that the Measure (located in the CP table/sheet/field) that should be doing the calculation, Total Courses In Program isn't updated.

 

Please let me know if you have any clarifying questions and I'll do my best to help clear things up. Thank you!

 

Link to the file (DAXTest.pbix): https://drive.google.com/open?id=1UH6ycRqRC9EPT2hXAx9Q8RQSd88MtMbE

TomMartens's avatar
TomMartens
Icon for Super User rankSuper User
6 years ago

Hey Anonymous ,

 

thanks for providing the pbix.

Inspired by this, I created a very simple data model, that contains  3 tables:

  • Employees
  • CP - this reflects the Program, Sub Group and of course the Course
    As a course can be part of multiple programs I created a unique identifier by concatenating the Program and Course columns
  • CP completion
    This reflects the progress of an employee regarding a certain Program, this table also has the unique identifier Program_Course

Here is a screenshot of the data model:

I created these measures:

 

Number of Courses = 
CALCULATE(
    DISTINCTCOUNT('CP'[Program_Course])
    , ALL('CP'[Program Sub Group])
)

 

 

 

Number of completed courses = 
IF(HASONEVALUE('Employee'[Employee])
    ,CALCULATE(
        COUNTROWS('Course Completion')
        , KEEPFILTERS('Course Completion'[Status] = "completed")
    )
    , BLANK()
)

 

 

 

Number of missing courses = 
IF(HASONEVALUE('Employee'[Employee])
    , [Number of Courses] - [Number of completed courses]
    , BLANK()
)

 

 

Depending on your analytical requirements it can become necessary to use the iterator function AVERAGEX(...) to create a reasonable number for the measures ...missing and ...completed. 

 

This allows to create a report like this:

Here you will find the pbix file:

https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/EfUbjneDvQRBin0IL_CUzyIBXUgtUy9RccwbqqTq0xlX6Q?e=3scJNB

 

Hopefully, this provides some ideas to tackle your challenges.
From my personal experience, the data model is key, and I tend to avoid m*n relationships between tables whenever possible, of course sometimes this is not possible 🙂

 

Regards,

Tom