Find the number of DISTINCTCOUNT of the overlap having 2 Categories.

[Mudlogger]

Hi Guys,

This question may sound simple but I could not find a solution here.

I have a table that has 2 columns User and Category,

I would like to find the number of how many unique people fall into each Category (T/F).

The problem is there are some users having both Categories.

I do not know the way to find this number.

you can see what I mean in the picture below. I do not know how to get the table on the right side.

Thank you in advance 

I am very appreciated for your answer.

[MahyarTF]

Hi,

Create a table visual and Drag the Category and User as a column, then change the user attribute as shown the distinct count :

[Mudlogger]

Hi MahyarTF,

Thank you for your answer.
As you can see F =4 , T = 5  is not equal to 7
So, I need to find the overlap in both T and F.

Please help.

[MahyarTF]

Change it to count, then the 9 is shown

[Mudlogger]

I would like to see a table like my picture above

T = 3

F = 2

***T and F = 2***

(Total will be 7)

[MahyarTF · Accepted solution]

His is my solution :

1- in Power Query, Create a copy of the particular table 

2- then, in User column, create a group as below :

3- Filter the Count just for show the Count = 2

4- in the main table, create the merge table :

5- then, tick just Count

6-in Power BI create a simple column :

NewCategory = if (Sheet123All[Sheet123 (2).Count] = 2, "T and F", Sheet123All[Category])

7- the develop the below table (count for User)

[Mudlogger]

Hi MahyarTF,

I do not know why I can not click "OK" on this step.

Please advise

[MahyarTF]

You have to select the link column (User) in each table.(just click on the title of the column in each table)

[Mudlogger]

Thank you MahyarTF, It works for me, and I will accept your answer as a Solution ^^