Forum Discussion
Count all shared values with condition
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
Please find the solution at :https://www.dropbox.com/s/m28qblkceaskoto/Entity_problem.pbix?dl=0
4 Replies
Hey Anonymous
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?
Please find the solution at :https://www.dropbox.com/s/m28qblkceaskoto/Entity_problem.pbix?dl=0
Thank you so much amitchandak , this is exactly what I was looking for!
