Forum Discussion

silverbullet1's avatar
silverbullet1
Regular Visitor
7 years ago

Count of Distinct Values not working

Hi there,

I have the following dataset...

Sample APR is a calculated column which contains randomly generated numbers.

APR Group groups these. So basically 5.X = 5, 6.X = 6, etc etc.

I want to create a third calculated columns which counts the number of distinct values in the Sample APR column, desired output like the following....

I naturally tried to use DISTINCTCOUNT, with the following DAX:

Num of Cases = DISTINCTCOUNT('myTable'[Sample APR])
 
However, when I use this, I get the enture column populated by the value of 11341 which will be the number of rows of data I have I suspect. I also tried DISTINCTCOUNT with APR Group column to see what the result was and this resulted in the entire columns being populated with the a value of 12 (incidently I noticed this number is the COUNT of the number of case statements I used to create the APR Group column).
Can anyone help me out with this please?
General Comment
Need Help

3 Replies

  • tex628's avatar
    tex628
    Icon for Community Champion rankCommunity Champion
    7 years ago
    Test = CALCULATE(DISTINCTCOUNT(Table1[Sample APR]),ALL(Table1), Table1[APR Group] = EARLIER(Table1[APR Group]))
    • silverbullet1's avatar
      silverbullet1
      Regular Visitor
      7 years ago

      Thanks tex628 

      That appears to be grouping the APR Group column rather than the Sample APR one though. Been trying variations of your solution but still no luck unfortunately.

      • tex628's avatar
        tex628
        Icon for Community Champion rankCommunity Champion
        7 years ago

        My apologies, i didnt read the initial post well enough!

        Column = CALCULATE(COUNTROWS(Table1) , ALL(Table1) , Table1[Sample APR] = EARLIER(Table1[Sample APR]) , Table1[APR Group] = EARLIER(Table1[APR Group]))


        Hope it works!