Count all shared values with condition

[rginny_]

Hi everyone,

I cannot seem to figure out how to do the below.

I have a table like this:

User	Entity
A	1
A	2
A	3
B	2
C	1
C	2
C	4
D	2
D	4

Essentially what I'd like to do is count how many times one entity appears with another entity based on users.

So I'd like to get all possible unique combination of entities (in two separate columns) and the count of times they appear with the same user (as a third column)- see the below table:

Entity 1	Entity 2	User Count
1	2	2
1	3	1
1	4	1
2	3	1
2	4	2
3	4	0

So for example the combination '1-2' has a user count of 2 because both A and C appear with entity 1 and 2.

On the other hand, the combination of '1-3' has a user count of 1 because A is the only person who appears with both entity 1 and entity 3.

I hope that makes sense and if someone could help me figure out how to get that second table I would be super grateful!

Many thanks,

Ginny

[amitchandak · Accepted solution]

Please find the solution at :https://www.dropbox.com/s/m28qblkceaskoto/Entity_problem.pbix?dl=0

[rginny_]

Thank you so much @amitchandak , this is exactly what I was looking for!

[Ashish_Mathur]

Hi,

You may download my solution file from here.

Hope this helps.

[Tad17]

Hey @rginny_ 

Check out this thread: https://community.powerbi.com/t5/Desktop/distinct-count-of-multiple-columns/td-p/433060

It is similar to what you are looking for though the result was measures which can be listed in a table or cards. Did you need it in a calculated table?