Forum Discussion

Aukje's avatar
Aukje
Helper I
6 years ago
Solved

Table with distinct count as row

Hi,   Question: How can I achieve a table with distinct count (0-15) as rows, with a count of occurrance (individual IDs with a distinct count value based on another column) as column value?   Eg...
Need Help
Tips and Tricks
  • Wesleyvanv's avatar
    Wesleyvanv
    6 years ago

    Hi Aukje,

     

    You should create a new Table in DAX:

    New Table = SUMMARIZECOLUMNS('Table'[ID],"Groups",DISTINCTCOUNT('Table'[GROUP]))

     

    And then the following measure:

    Count = COUNTROWS('New Table')

Aukje's avatar
Aukje
Helper I
6 years ago

Hi,

 

However than  I will get distinct count per ID, I rather would like to achieve an aggregated value (numbre of IDs) with that particular distinct count. (Reverse column A and B in your lower example, and make a count of ID)

Wesleyvanv's avatar
Wesleyvanv
Advocate I
6 years ago

Hi Aukje,

 

You should create a new Table in DAX:

New Table = SUMMARIZECOLUMNS('Table'[ID],"Groups",DISTINCTCOUNT('Table'[GROUP]))

 

And then the following measure:

Count = COUNTROWS('New Table')

  • Aukje's avatar
    Aukje
    Helper I
    6 years ago

    Thanks for your help. I thought it was solved but I am running into a discrepancy.

     

    I created one table just to know distinct count per group (per gorup A, per group B) etc, and then the table above according to the specifications to see how many IDs occured in more than one group:

    First table

    Groups 

    Blank 829

    A 225

    B 658

    C 124

    ..

    Distinct count of ID: 9 511

     

    Second table as you described

    Blanks 2923

    1  123

    2  234 

    3  456 

    4 ....

    Distinct count of ID: 9 511

     

    Suddenly my blank value is much higher, these refer to IDs not occuring in any group, however they in my opinon cannot be more than the 829 in the first table. Does anyone know what could be the problem?