Count Number of Times Players Play Together in Games

[Anonymous]

New to Power BI and been doing a bit of research on the product.  Making good progress on my project but ran into brick wall on one calculation.  I would like to see how many times two players have played together in a game.  Here is my table layout:

. 

GameId	PlayerName
1	QB
1	WR1
1	WR2
1	RB1
1	TE
2	QB
2	WR1
2	RB2
2	WR3
2	RB3
3	QB
3	WR1
3	RB3
3	K
3	WR4

Here are the results I expect to get.

Combo - TimesPlayedTogher
QB/WR1 - 2
QB/RB1  - 1
QB/WR2  - 1
QB/K - 1
QB/TE  - 1
QB/WR3  - 2
QB/RB2  - 1
QB/RB3  - 2
QB/WR4  - 1

I just can't seem to output this result with two players for the same game counted as one unit.  Been playing around with several different measured formulas.  Any thoughts?

[OwenAuger]

Hi @Anonymous 

I had an initial look at this and it fits the Basket Analysis pattern:

https://www.daxpatterns.com/basket-analysis/

I created up a sample PBIX here showing how you could handle this:

1. Create a data model looking like this, with the table you posted being called Games, and dimension tables for Player and Player Secondary

2. Note that there is an inactive relationship between Games[PlayerName] and 'Player Secondary'[PlayerName Secondary].

3.  Create these measures:

   Times Played Together = 
   CALCULATE ( 
       DISTINCTCOUNT ( Games[GameId] ),
       CALCULATETABLE ( 
           SUMMARIZE ( Games, Games[GameId] ),
           ALL ( Player ),
           USERELATIONSHIP ( Games[PlayerName], 'Player Secondary'[PlayerName Secondary] )
       )
   )

   PlayerName < PlayerName Secondary = 
   INT ( SELECTEDVALUE ( Player[PlayerName] ) < SELECTEDVALUE ( 'Player Secondary'[PlayerName Secondary] ) )

   Combo = 
   SELECTEDVALUE ( Player[PlayerName] ) & "/" & SELECTEDVALUE ( 'Player Secondary'[PlayerName Secondary] )

4. Then create visuals with fields Player[PlayerName], 'Player Secondary'[PlayerName Secondary], [Combo] and [Times Played Together].
5. Include a visual level filter [PlayerName < PlayerName Secondary] = 1
6. In my example PBIX I created a table with PlayerName & PlayerName Secondary hidden, so you can see Times Played Together by Combo.
7. Note - you could also create a table containing the Combo values and use this to drive the filtering. This might complicate the data model & formulas a little (in my opinion) but could be done.

Hopefully that's useful as a start.

Regards,

Owen

[Anonymous · Accepted solution]

Thank you good sir.  I had all my data in a single table and when I tried mimic what you had done I got some bizaare results.  I tried breaking up the data but no luck.  But I was able to take my data and build off template you created.  I'm still learning so maybe one day I'll figure out why my source data didn't work.