Multi-Degree Member-to-Member Relationships

[RubenB]

I'm dealing with a problem where I'm trying to visualize relationships between people. All people are on the same level, and there's no particular hierarchy between them, meaning that any person in the data can be related to any other person in the data.

My (simplified) model consists of a "People" table for all unique people and their city, as well as a "Relationships" table where all relationships are documented in From-To relationship with a "Type" identified (e.g.: Friend, Partner, Coworker,...).

Currently, the Relationship table contains each relationship in both directions. Meaning that if Person 1 and 2 are friends, there are 2 entries in the Relationship table. One where Person 1 is the From ID and Person 2 is the To ID, and one with the opposite. However, there is room to alter or optimize this data model if necessary.
Here's what this simplified model looks like:

In the data visualization layer, the goal is to provide a single-select dropdown of all people in the People table, and then visualize all the people they are related to.

Here's a simplified representation of what this could look like:

I have the following requirements for this report:

1. Display the city name of the TO person in the relationships grid.
   I've tried creating an inactive relationship between the To ID in the relationship table and the ID in the People table, and activating that relationship in a measure, but that was not successful.
2. How can I expand the relationship grid to display relationships to the second, third, fourth,... degree from the selected person? Meaning, if Person 3 is a friend of the selected Person 2, I want to add on an additional column which shows all the relationships of Person 3, by duplicating the entry for Person 3 as many times as they have relationships in the data. For each of these relationships I would also want to display the relationship type and the city of the To person.

I appreciate any help I could get on this!

Thanks,

Ruben

[sayaliredij · Accepted solution]

HI @RubenB 

I have made adjustments to address the initial error in the Power BI model.

1. Presently, an active relationship has been established between the 'from_id' and 'id' columns in the People table, facilitating the comprehensive visibility of all users.

2. Which also enable us not to have any specific city measure

3.

considering that the data involves employee or people names, the impact may be relatively minimal, particularly if the dataset does not scale to billions of records.

I recommend assessing the performance with larger datasets to validate the effects of the lookup removal.

Here is new power bi file - People-Relationship.pbix

4. for goingt multi-degree aspect, i dont have first hand experience but for me this sounds like case of recusrsive DAX which is not possible.

If you have possibility to play around source system 

i would try to prepare new relationship table like following

Thanks and Regards,

Sayali

Please mark the question solved when done and consider giving a thumbs up if posts are helpful!! 

[RubenB]

Thank you for your help, @sayaliredij. This definitely put me on track of working through the issue.

I agree with you that the recursive requirements on multi-degree relationships is going to require a revision of the source data. Your mock-up makes a lot of sense.

[RubenB]

Hello @sayaliredij,

Thank you very much for your help on this. I found your feedback very insightful.

I like your suggestion to make both relationships between People and Relationships table inactive and control the activation and deactivation of the relationships through measures. It does provide more flexibility on the model to visualize the data as needed.

I did run into a few issues when implementing your approach:

1. Since you made the slicer leverage the "From Person Name" calculated column, instead of the name field on the People table, it now doesn't show any people that don't have any relationships at all. I should have been more clear that this is a requirement, since there are other visuals in my report that show other metrics for these people, even if they don't have any relationships to other people in the network.
2. My understanding from your approach is that you are mainly adding on additional columns to the relationship table to represent details about the People table inside the relationship table. I think that approach could definitely work, but I'm a little bit concerned about the performance of these LOOKUPVALUE columns, because I'm dealing with multiple hundreds of thousands of records in the People table and multiple millions of records in the relationship table. Do you know if there's a way to visualize the data without duplicating People-level columns into the Relationships table?
3. Do you have any recommendations how to go into the multi-degree aspect of the requirements I'm dealing with, in order to visualize multiple layers of relationships. For example, when filtering on Person A, how can I visualize that Person D is a friend of Person C who is a coworker of Person B who is a family member of Person A?
   At this point I'm considering to add these "indirect" relationships as records into the relationships table, with a PATH column to indicate how the relationship is established. But I'm again concerned about the performance of an architecture like that.

Thank you again for your helpful insights and for taking the time to reply to my thread. Any further feedback would be highly appreciated!

Thanks,

Ruben

[sayaliredij]

Hello @RubenB ,

To address the given issue, I implemented the following approach:

1. Table Relationships:

   • Established two connections between the 'People' and 'Relationship' tables:
     1. Relationship[From Id] <-- People[Id]
     2. Relationship[To Id] <-- People[Id]
   • Note: Both of these relations are set as inactive.

2. Calculated Columns in Relationship Table:

• Created calculated columns in the 'Relationship' table to display the names of the individuals (From and To).
  • From Person Name:
    From Person Name = LOOKUPVALUE( 'People'[Name], People[Id], Relationship[From Id] )
  • To Person Name:
    To Person Name = LOOKUPVALUE( 'People'[Name], People[Id], Relationship[To Id] )

    3. Measure for Selected Person's City:

    • Formulated a measure to determine the city of the selected person:
      From Person City = CALCULATE(MAX(People[City]), USERELATIONSHIP(People[Id], Relationship[From Id]))

      4. Power BI Visuals:

      • Utilized the above components to create visuals on the Power BI page, showcasing the relationships and associated information.
    • 
  • Please find the pbix file here - 

Thanks and Regards,

Sayali

Please mark the question solved when done and consider giving a thumbs up if posts are helpful!!