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
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

we have distinctcount to count distinct items. Do you want to count unique rows. means are there duplicate rows?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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?

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

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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.