Counting Number of Partnerships

[evan_hoefling]

I have a table with structure similar to this:

project-contact

project	contact
1	Steve Smith
1	Sally V.
1	Sammy Davis
2	Steve Smith
2	Sally V.
3	Tom Rosen
3	Sammy Davis
4	Tom Rosen

I am looking to ideally get to something that is like this:

person 1	person 2	partnerships
Steve Smith	Sally V	2
Steve Smith	Sammy Davis	1
Sally V	Steve Smith	2
Sally V	Sammy Davis	1
Sammy Davis	Steve Smith	1
Sammy Davis	Sally V	1
Tom Rosen	Sammy Davis	1

Essentially I am trying to count the number of times a person is partnered with another on projects.

[OwenAuger · Accepted solution]

Hi @evan_hoefling 

I recommend something similar to the Basket Analysis pattern described on DAX Patterns.

I have attached a sample PBIX, using a tweaked version of the above pattern.

Firstly, your data model can be set up like this, where Person 2 is a copy of Person 1, which contains a list of distinct people:

Then you can create measures like this.

• The Partnerships measures are designed to be placed in a visual grouped by Person 1 and Person 2.
• Partnerships will include both partnerships (A,B) & (B,A).
• Partnerships without duplicates will avoid double-counting and just include (A,B).

# Projects = 
DISTINCTCOUNT ( Projects[project] )

Partnerships = 
VAR ProjectsWithBothPeople =
    CALCULATETABLE (
        SUMMARIZE ( Projects, Projects[project] ),
        REMOVEFILTERS ( 'Person 1' ),
        -- Exclude Person 1 = Person 2
        EXCEPT (
            VALUES ( 'Person 2'[Person 2] ),
            VALUES ('Person 1'[Person 1] )
        ),
        -- Activate relationship with Person 2 
        USERELATIONSHIP (
            'Person 1'[Person 1],
            'Person 2'[Person 2]
        ) 
    )
RETURN
    CALCULATE (
        [# Projects],
        KEEPFILTERS ( ProjectsWithBothPeople )
    )

Partnerships without duplicates = 
-- Assume single value of Person 1
VAR CurrentPerson1 =
    SELECTEDVALUE ( 'Person 1'[Person 1] )
VAR ProjectsWithBothPeople =
    CALCULATETABLE (
        SUMMARIZE ( Projects, Projects[project] ),
        REMOVEFILTERS ( 'Person 1' ),
        -- Ensure each pair appears only once
        KEEPFILTERS ( 'Person 2'[Person 2] > CurrentPerson1 ), -- Person 2 > Person 1
        -- Activate relationship with Person 2
        USERELATIONSHIP (
            'Person 1'[Person 1],
            'Person 2'[Person 2]
        ) 
    )
RETURN
    CALCULATE (
        [# Projects],
        KEEPFILTERS ( ProjectsWithBothPeople )
    )

Hopefully that's of some use.

Regards,

Owen