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
v-gizhi-msft's avatar
v-gizhi-msft
Community Support
6 years ago

Hi,

 

Please try this:

 

Total Courses in a Program =
IF ( HASONEVALUE ( CP[Column1] ), CALCULATE ( COUNTROWS ( CP ), ALL ( CP ) ) )

 

 

Best Regards,

Giotto Zhi 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.

  • TomMartens's avatar
    TomMartens
    Super User
    6 years ago

    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

    • 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
        Super 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