Creating visual to show overlap between groups?

[cmc099]

I have a table that has the names of people and the groups that they belong to. For example, the table looks like the following:

Worker name	Group name
John Henderson	Economy
John Henderson	Finance
John Henderson	IT
Scott Smith	Economy
Scott Smith	IT
Scott Smith	Sales
Scott Smith	Marketing
Denise Lockman	Sales
Denise Lockman	Finance

What I want to do is create a visualisation such that I have a slicer that I can select multiple names from, and the groups that these names all belong to are displayed. For example, if I chose John Henderson and Scott Smith, Economy and IT would be displayed, if I chose Scott Smith and Denise Lockman, Sales would be displayed, if I chose John Henderson, Scott Smith and Denise Lockman, no groups would be displayed etc.

I have tried duplicating the table and creating another table with the intersection between the two tables but I could not get it to work. I also tried pivotting out the column 'Group name' and filled the new columns with 1 and 0's depending on if they belong to each group or not, but that also does not work (I should also mention that in my real data set I have over 400 unique names and 400 unique groups).

I'm sure there is a simple way of displaying what I want but I cannot figure it out. Would anyone know how I might go about this?

[DataInsights · Accepted solution]

@cmc099,

This solution uses a separate table for the visual in order to break the link between slicer and visual. The original table is named Workers.

1. Create calculated table:

WorkersVisual = Workers

2. Create measure:

Visual Filter = 
VAR vWorkerCountSlicer =
    COUNTROWS ( VALUES ( Workers[Worker name] ) )
VAR vWorkerGroup =
    TREATAS ( VALUES ( WorkersVisual[Group name] ), Workers[Group name] )
VAR vWorkerCountInGroup =
    COUNTROWS ( CALCULATETABLE ( VALUES ( Workers[Worker name] ), vWorkerGroup ) )
VAR vResult =
    IF ( vWorkerCountSlicer = vWorkerCountInGroup, 1 )
RETURN
    vResult

3. Create a slicer using Workers[Worker name].

4. Create a table visual using WorkersVisual[Group name].

5. Add the measure Visual Filter to the table visual as a filter:

Result:

---

---

[cmc099]

This is perfect! Thanks very much!