Count/Distinctcount in Columns as a New Table/Visual

[cgoetze]

Hi,

I need help to Distinctcount and group the result as a simple table.

Table Structure:

Table1 
Name - ID
John - X234
John - W567
John - 8910
Maria - W567
Maria - X234
Leo - 2345
Leo - 6789
Leo - X234
Richard - X234
Richard - W567
Richard - 9012
Richard - 6789
Alex - X234
Alex - W567
Alex - 8910
Tiffany - W567
Tiffany - X234
Marc - 2345
Marc - 6789
Marc - X234
Rafael - X234
Rafael - W567
Rafael - 9012
Rafael - 6789

Result as Visual (table)

Table A
Count ID - Count Names
2 - 2
3 - 4
4 - 2

In other table a range of names in every ID

Table B
Range count Names - Count ID
1-3 - 3
4-6 - 2
>7 - 1

Thanks.

[cgoetze]

Hello,

Thanks!!!

[v-rongtiep-msft · Accepted solution]

Hi @cgoetze ,

I have created a simple sample, please refer to my pbix file to see if it helps you.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cgoetze]

Table1
Name - ID
John - X234
John - W567
John - 8910
Maria - W567
Maria - X234
Leo - 2345
Leo - 6789
Leo - X234
Richard - X234
Richard - W567
Richard - 9012
Richard - 6789
Alex - X234
Alex - W567
Alex - 8910
Tiffany - W567
Tiffany - X234
Marc - 2345
Marc - 6789
Marc - X234
Rafael - X234
Rafael - W567
Rafael - 9012
Rafael - 6789

Result:
TableA
Count ID - Count Name (Distinct)
1 - 0
2 - 2
3 - 4
4 - 2
5 - 0

In the table above we have 0 names with 1 ID, 2 Names with 2 IDs....

*The numbers 1 and 5 could be omitted.

In excel i could do a dinamic table with Name in line and count ID in value, and after that a table that "count if" using the dinamic table as source.

Thanks.

Name	ID
John	X234
John	W567
John	8910
Maria	W567
Maria	X234
Leo	2345
Leo	6789
Leo	X234
Richard	X234
Richard	W567
Richard	9012
Richard	6789
Alex	X234
Alex	W567
Alex	8910
Tiffany	W567
Tiffany	X234
Marc	2345
Marc	6789
Marc	X234
Rafael	X234
Rafael	W567
Rafael	9012
Rafael	6789

For TableA

For TableB

[cgoetze]

Hi,Is a Statistics Analysis, not so related with business. But I tried my best.

>>>

Data 

[VijayP]

@cgoetze 

This question is bit vague. 

when you create distinct count of both columns answer will not be more than 1 row. Try  to add some data with business perspective and also add why you want to see that!