Forum Discussion
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.
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).
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')
4 Replies
Hi Aukje,
You can select distinct count in a table with the right mouse click on the value in the Visualizations pane.
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)
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')
