Soccer stats and relations

[DanielRA81]

Hi all,

I have recently started using PowerPivot to analyze historical soccer results and so far I have been enjoying working with the tool. However, I have come across one obstacle that I can't seem to get past so I hope you are able to help me out.

Currently my PowerPivot model has two tables; Results and Date.

The Results table consists of the following columns where each row represents one game played:

[TABLE="width: 573"]
<colgroup><col><col><col span="2"><col span="3"></colgroup><tbody>[TR]
[TD]Div[/TD]
[TD]Date[/TD]
[TD]HomeTeam[/TD]
[TD]AwayTeam[/TD]
[TD]FTHG[/TD]
[TD]FTAG[/TD]
[TD]FTR[/TD]
[/TR]
[TR]
[TD]E0[/TD]
[TD="align: right"]13/08/2011[/TD]
[TD]Blackburn[/TD]
[TD]Wolves[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]E0[/TD]
[TD="align: right"]13/08/2011[/TD]
[TD]Fulham[/TD]
[TD]Aston Villa[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]E0[/TD]
[TD="align: right"]13/08/2011[/TD]
[TD]Liverpool[/TD]
[TD]Sunderland[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]E0[/TD]
[TD="align: right"]13/08/2011[/TD]
[TD]Newcastle[/TD]
[TD]Arsenal[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]D[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 573"]
<colgroup><col><col><col span="2"><col span="3"></colgroup><tbody>[TR]
[TD]
The Results table contains matches from many different divisions and seasons.

[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The Date table consist of the following columns:
Date, Month, Year, Weekday, Season

I have made a relation between the two tables using "Date" column as key. This works just fine and in my pivot table I am able to filter on dates, seasons, etc. and all measures (points, games, goals, etc.) works just fine.

But...it only work to the extent that when I select a team to see data for, I use either AwayTeam or HomeTeam and hence, I only get data for the selected team's away or home games depending on what I choose. This is as expected but I would like to be able to select a specific team and then having all it's games shown - both away and home. And at the same time, all filters on dates, seasons, etc. should still work.

And..this is where I am stuck as I cannot seem to find out how to make this possible. I have tried to create a "Team" table but I am not able to relate it to the Results table in the PowerPivot window. I get the error message "The relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique values."

So...this is where I hope you can help me out. Do I need an intermediate table, should I use link tables, can I create measures that would give me what I am looking for or...?

Many thanks your any help or guidance you can provide.

BR
Daniel

 

[Jacob Barnett]

Daniel, welcome to the forum! Great subject although I must insist we call it Football!

The team table idea will work in exactly the same way as your date table and is crucial in this kind of scenario however the team table must contain only unique values for teams - then you will be able to relate it to the results table above.

The problem you are going to have from that point is the structure of the results table - a more normal construction would be one record per team per game that would show goals scored / conceded as well as whether they were the home or away team and who the opposition was.

Hope this helps.
Jacob

 

[DanielRA81]

Thanks for the reply Jacob - and I am fully on board in terms of the use of Football

I still cannot get the relation between the team table and result table to work even though the team table only consist of one column with the distinct team names. Also I assume I need to use either HomeTeam or AwayTeam in the result table but in either case am I allowed to create the relationship.

I see your point in having one record per team per game but it will require a lot of manipulation of the existing data as the data is provided on a match by match basis so I would really love it if it was possible to do some magic with the given data structure.

If this is not possible, I guess I should build a "Game" dimension and a "Game" fact based on the information I currently have in the Results table and then subsequently skip the results table.

Thanks
Daniel

 

[Jacob Barnett]

Daniel,

What I've found is that PowerPivot can be extremely fussy about what it considers to be duplicates and I am 99% convinced that it thinks you have a duplicate in your team table.

There is a technique that allows you to use multiple relationships involving disabling those relationships and using the USERELATIONSHIP() function however it is not simple and even if you got it working on a basic level it would make everything you subsequently do super complicated! I'd put your efforts into learning some of the more useful DAX stuff like CALCULATE(), FILTER() and ALL() etc.

If you are looking for magic then its probably going to be in converting the results table to something more useful without the pain! Is the data source an excel file? If so I can very easily help you with some VBA that 'flattens' the data!! If its via SQL then there is an unpivot function that will help.

If you can share the workbook via SkyDrive/DropBox/GoogleDrive then I would be happy to have a look (alternatively share the results table/source and I can build my own).

Jacob

 

[Laurent C]

You will need a lookup table for all your teams. If you want to create one using DAX, you may want to take a look at my post, here:DAX Queries in Excel 2013 : using ROLLUP to cover some UNION scenarios « The Data Specialist
As Jacob said, you will need to define two relationships from your data table to your lookup table. Since you can have at most one active relationship, one of them will be inactive.

Implementing measures that make use of the information in both columns - like total number of games played - will require you to use CALCULATE and USERELATIONHSIP, to use the inactive relationship for the time of your calculation.

Assuming your lookup table is called Teams and has a [Team] column, and assuming the inactive relationship is the one going from the [AwayTeam], calculating the total number of games played, would look like this:

[Total Games Played] = COUNTROWS('Results') + COUNTROWS('Results', USERELATIONSHIP( Teams[Team], 'Results'[AwayTeam] ) )

 

[DanielRA81]

Jacob,

Many thanks for your insights and offer to help with the VBA 'magic'. I have sent you a pm with a link to Google Drive where the relevant files are shared.

BR
Daniel

 

[DanielRA81]

Laurent,

Many thanks for link you provided on the possibilities in using DAX to build a lookup table for all teams. As the number of teams is relatively small it is not a big hassle to build and maintain a lookup table manually so I will probably stick with that initially.

I have not used the USERRELATIONSHIP function yet so thanks for providing the formula for the Total Games Played measure.

BR
Daniel