calculating values from multiple columns

[Rampeur]

Hi Guys,

I have a dataset with a list of football games.

Games List:

Home Team	Away Team
Sweden	France
France	USA
Canada	France
Sweden	Canada

I have few questions about it.

• I have calculated the numbers of games played by each countries with this way:
  I have Created a new table (Lis of countries) in order to have the full list of countries (Distinct & Union). Then I have created a new column.
  
  
  Games Played = CALCULATE(COUNTROWS('Games List'),FILTER('Games List','Games List'[home_team]=EARLIER('List of Countries'[Countries]))) +

  CALCULATE(COUNTROWS('Games List'),FILTER('Games List','Games List'[away_team]=EARLIER('List of Countries'[Countries])))

  Was it the right way to do it?
   
• ThenI have created a new columns:
  Match = Home Team &"-"&  Away Team

From this column I would like to calculate few things:

• I have calculated how many time a games happened. (i.e: Sweden - France: 3 times. France - Sweden: 6 times)
• Then, I also would like to calculate how many time sweden played again France (either home or away)
  I would like to make appears in vizualisation like France - Sweden: 9 but Also: If you filter on Swden there is a Graph with the number of times they played a again each team
  
  France: 9
  Brazil 6
  Norway 5
  and so on.

Thank you in advance for those who will have a look. Thank you so much 🙂

Maxime

[V-lianl-msft]

Hi @Rampeur ,

Create the home and away tables as slicers,then create measures like below:

home or away = CALCULATE(COUNTROWS(FILTER('Games List','Games List'[Home Team]=SELECTEDVALUE(Home[Home Team])&&'Games List'[Away Team]=SELECTEDVALUE(Away[Away Team]))))

either home or away = CALCULATE(COUNTROWS(FILTER('Games List','Games List'[Home Team] in {SELECTEDVALUE(Home[Home Team]),SELECTEDVALUE(Away[Away Team])}&&'Games List'[Away Team]in {SELECTEDVALUE(Away[Away Team]),SELECTEDVALUE(Home[Home Team])})))

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Rampeur]

Thank you for answering but this is not what I want.

How would you create a slicer (1 single one) with the list of countries and Then you get a bar chart with the top 5 of teams they play against.

so you filter on sweden, then you get 

France: 9
Brazil 6
Norway 5

for example.

[Ashish_Mathur]

Hi,

You should first of all Unpivot your dataset so that all teams appear in a single column and another column (with the title of Attribute) specifies the type of team.