Relationship betwen people

[daveburke114]

Hi everyone, I've just started using PowerPivot but not sure if this question is in the right place.

I have a small project I'm working on and I basically need to work out the likelihood that people know each other. (To keep it simple I won't go into the whole thing)

I have one table with customers names/addresses/email etc
[TABLE="width: 500"]
<tbody>[TR]
[TD]CustomerID
[/TD]
[TD]First Name
[/TD]
[TD]Surname
[/TD]
[TD]Address1
[/TD]
[TD]Address2
[/TD]
[TD]Address3
[/TD]
[TD]Postcode
[/TD]
[TD]eMail
[/TD]
[/TR]
[TR]
[TD]542645
[/TD]
[TD]Tom
[/TD]
[TD]Evans
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]784579
[/TD]
[TD]Simon
[/TD]
[TD]Jones
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have one table with a list of events on and a list of people who attended.
[TABLE="width: 500"]
<tbody>[TR]
[TD]EventID
[/TD]
[TD]EventName
[/TD]
[TD]Address1
[/TD]
[TD]Address2
[/TD]
[TD]Address3
[/TD]
[TD]Postcode
[/TD]
[TD]Attendee
[/TD]
[/TR]
[TR]
[TD]784554
[/TD]
[TD]SocialClub
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]542645
[/TD]
[/TR]
</tbody>[/TABLE]


Obviously with all the fields filled in! So any event is listed as many times as attendees.

If two people in my customer list have attended the same party there's a chance they might know each other. If they have both attended lots of the same parties they are much more likely to know each other. Similarly, if they have both attended a party of 5 people they are much more likely to have spoken than one event of 100 people!

So I need to take into account how many events they appear on the same list, and how many people in total attended the event.

I'm not sure how to proceed with this one. One ideas was to 'weight' the importance of an event with how many people attended (the smaller the better).

Any ideas are very much appreciated!
I apologise if this is in the wrong section and there's a much better way to do this!

Thanks
Dave

 

[Jacob Barnett]

Dave, this isn't entirely straightforward but can definitely be done. Will post something this week.................

 

[daveburke114]

Hi Jacob, I thought it was just complicated because I was new to PowerPivot, Not even sure where to start with this one

Any help at all would be amazing

Thanks
Dave

 

[Laurent C]

Regarding the number of people who attended an event, you can do a DISTINCTCOUNT on the [Attendee] column in your EventDetails table (I picked a name for the sake of clarity). I will leave it to you "as an exercise" to do it.

You might need to import the Customers table twice to make this work. Add a relation from your EventDetails table to Customers1 and one to Customers2. This will allow you to select 2 different customers and see their behaviour.

The measure could look like that:

[Number of common events] =CALCULATE(
DISTINCTCOUNT(EventDetails[EventID])
, ALL(Customer1), ALL(Customer2)
, CALCULATETABLE(
VALUES('EventDetails'[EventID])
, ALL('Customer1')
)
, CALCULATETABLE( VALUES('EventDetails'[EventID])
, ALL('Customer2')
)
)

Note that in case you have several values selected for Customers1 and Customers2, this will calculate the number of events that any one person selected in Customers1 and any one person selected in Customers2 attended.

This may not be the behaviour you expect. In that case, let us know how you would like the pivot table to behave (measures and type of selection).

 

[daveburke114]

Hi Laurent, many thanks for your reply, I've just got a few questions. I apologise in advance if they are all beginner questions!


When you say I may have to import the customer details twice, I now have two tables in PowerPivot, one called Custmer1 and one called Customer2. I have then created a relationship between EventDetails[Attendee] and Customer1[CustomerID] Then a second link between EventDetails[Attendee] and Customer2[CustomerID]

Then I have created a new column after [Attendees] in the EventID table with your code in. The column is titled Number of common events.

Its producing errors, and I'm not quite sure which part I'm doing wrong. How does the measure know which two customers I want to see results for?

, ALL(Customer1), ALL(Customer2)

I have tried changing this to , ALL(3000001), ALL(300002)
This is in an attempt to directly reference two CustomerID numbers. But that's not worked. Also I noticed that in the code:

, ALL(Customer1), ALL(Customer2)

, ALL('Customer1')

, ALL('Customer2')

In one it has ' ' and one it doesn't. Is that because they are referencing two different things that happen to both be called 'Customer1' or should there be quotes in both? etc

For this part of the project, it would be great to have a cell at the top (or a slicer) where I can select one customer, and order the results to show all the other customers in decending order of how many events they have attended together.


Sorry for all the questions and thanks again for your help
Dave

 

[Laurent C]

I edited the formula after I - briefly - tested it, to rename things according to your problem description and to turn it into an english formula. In the process, some errors may have slipped in. I am sorry if this is the case.

Discrepancies in the usage of single quotes '' was just sloppiness on my side. Single quotes are only mandatory if your table name contains specific caracters (a space, for example).

Let me also note that your description of what you want to obtain confirms you will need two dimensions for your customers.

If you want to check values for a specific customer, you can simply replace ALL('Customer1') with

FILTER(ALL('Customer1'), [CustomerID] = 3000001)

or simply use the shortcut expression

'Customer1'[CustomerID] = 3000001

Note that my expression was intended as a measure and not a calculated column. Can you be more specific about the "produced errors"?

How does the measure know which two customers I want to see results for?

The construct I suggested treats both customers symmetrically. If you select a customer on each dimension, you will see results for both customers. That is, the number of events they both attended.