DanielRA81
New Member
- Joined
- Feb 10, 2013
- Messages
- 5
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:
<colgroup><col><col><col span="2"><col span="3"></colgroup><tbody>
</tbody>
<colgroup><col><col><col span="2"><col span="3"></colgroup><tbody>
</tbody>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
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:
Div | Date | HomeTeam | AwayTeam | FTHG | FTAG | FTR |
E0 | 13/08/2011 | Blackburn | Wolves | 1 | 2 | A |
E0 | 13/08/2011 | Fulham | Aston Villa | 0 | 0 | D |
E0 | 13/08/2011 | Liverpool | Sunderland | 1 | 1 | D |
E0 | 13/08/2011 | Newcastle | Arsenal | 0 | 0 | D |
<colgroup><col><col><col span="2"><col span="3"></colgroup><tbody>
</tbody>
The Results table contains matches from many different divisions and seasons. |
<colgroup><col><col><col span="2"><col span="3"></colgroup><tbody>
</tbody>
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