cross frequency calculation

[Joop]

Hello community,

I have a question related to the frequency golf players are linked to each other during a competition.

Imagine players are grouped together in several flights during one day, and grouped together differently in flights the next day. This continuous during the season. Now I want to evaluate the times players have met each other during this period.

The following is an example, plus what I think should be the (format of the) result.

I have tried this in Power BI using Grouping, Pivot tabling and Indexing, but I’m stuck in getting the correct result. In real, there are a couple of thousand records a year.

There are also a few additional columns with data like gender, member or guest, etc used for filtering, but I think the main given columns will do for the explanation.

 Does someone have an idea how to solve this?

Could this easier be accomplished in Excel as an alternative?

Thanks for any help!

[Anonymous]

My gut here is you will need to duplicate your player table, so that you can have both player A and player B on the chart at the same time... without "impacting" each other.  I suspect if you do that... the rest becomes fairly easy.

[Joop]

Hi Scottsen,

thanks for your reply.

Unfortunately I don't know exactly how to see this duplicate table.

I want to see eventually, as an example, that player BINV1 has played with Bost 6 once, with PEER 3 three times, RABA 3 once and SPIT 1 also once. Also, as a consequence, that BINV 1 never played with BOOR 3, KLOM 1, ROST 5 and TAMI 9.

How would I do this?

Thanks again.

Joop

[Anonymous]

I literally mean duplicating the table.  So, instead of Players, you would have 2 tables "Home Players" and "Visiting Players" -- or similiar.

The easiest way to do that is probably to launch the Query Editor (on Home ribbon, Edit Queries), right click on your table (say Players) and choose "Reference".  That will create another table called Players2, you can rename as appropriate.  Save, done.

[Joop]

Hello Scottsen,

yes, I did duplicate the table, but then for the next steps I'm not sure what to do ...

Could you give me some more hints on how to proceed?

Thanks,

Joop

[Anonymous]

Tried to throw together a sample model here: Golfing.pbix

In fairness, this measure was... non-trivial.  I'm actually wondering if somebody else has a better/easier way.

Together = IF (HASONEVALUE(Away[Person]) && HASONEVALUE(Home[Person]) && VALUES(Home[Person]) <> VALUES(Away[Person]), 
      SUMX(
           SUMMARIZE(Tee, Tee[Date], Tee[Flight], "Together", IF(CONTAINS(Tee, Tee[Player], VALUES(Away[Person])) && CONTAINS(Tee, Tee[Player], VALUES(Home[Person])), 1, 0)),
           [Together]
      )
)  

[Joop · Accepted solution]

Hello Scottsen,

does seem indeed not being trivial !

However, this will definitely work for me. I will build it in my tables using your sample tables and statement.

Thanks very much again!

Cheers,

Joop