Table with distinct count as row

[Aukje]

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.

ID / GROUP

1 A

1 A

2 B

3 A

3 B

4 A

4 B

4 C

4 A

Output table:

0 0

1 2 

2 1

3 1

Table should output number of IDs with a certain distinct count value (of group in this case). Eg in the above example two IDs (ID 1 & 2, have one for distinct value of group, ID 3 has distinct value 2, and ID 4 has distinct value 3).

[Wesleyvanv]

Hi Aukje,

You can select distinct count in a table with the right mouse click on the value in the Visualizations pane.

[Aukje]

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 · Accepted solution]

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]

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?