How to Calculate highest matching values in fact table

[SanderTK]

I am building a simple report for me and my fellow pubquiz members to gain some insights into our scoring, ranking etc.

I have made a fact table which shows the date, score,number of players, and individuals that were playing that day.

Screenshot: 

What I now would like to calculate is for specific individuals in the team, who they have played the most quizes with.

I have already made a measure to calculate how often each player has played the quiz but I can't seem to figure out how to get to that last step.
I have thought of redesigning the fact table to set the player names as columns and setting a "1" when they played a specific date and leave it NULL when they didn't. That way I could also link dimension tables to it for each specific player. I don't know if it will help me reach my current goal though.
Screenshot of alternative fact table (in case it helps):

[MAwwad]

One way to find out which players an individual has played the most quizzes with is to pivot your data so that each player becomes a column, and then use a pivot table to count the number of times each player appears in the same row as the individual you're interested in.

To do this, you can follow these steps:

1. Select the entire range of data in your fact table.
2. Click the "Insert" tab in the ribbon, and then click the "PivotTable" button.
3. In the Create PivotTable dialog box, make sure the selected range is correct, and then choose where you want to place the pivot table.
4. In the PivotTable Fields pane on the right, drag the "Player" field to the Rows area and the "Date" field to the Columns area.
5. Drag the "Player" field to the Filters area and use the filter to select the individual player you want to analyze.
6. Drag the "Player" field to the Columns area again. This will create a column for each player, showing how many times they played on the same date as the individual you selected in the filter.

This will give you a pivot table that shows the number of times each player played with the individual you selected. You can then sort the pivot table by the counts in the player columns to see which players the individual has played the most quizzes with.

Alternatively, you could use a formula to count the number of times each player appears in the same rows as the individual you're interested in. To do this, you could use the COUNTIFS function, which allows you to count the number of cells that meet multiple criteria. For example, you could use a formula like this:

=COUNTIFS(A:A, "player1", B:B, "player2")

This formula would count the number of rows where the value in column A is "player1" and the value in column B is "player2". You can use this formula to count the number of times each player appears in the same rows as the individual you're interested in, and then use a pivot table or other method to sort and analyze the results.