Counting Games Played/Won/Lost against opponents from table of results

[paulvans182]

Good morning, 

I hope someone can help me with this.  I have loaded into Power BI a table of results from games played, the table looks as follows:

A bit of an explanation of the data.  For each Game, there are 2 or more players, each with their own game board.  The players earn points (Value column) for each attribute.  The Results column identifies the Winner, and the Loser(s) for each GameID.

I would like to build a report where the user can select a Game Board, and then it will show a table that identifies the success (count of the Results column) of that game board against the other game boards. 

I have mocked up a table in excel that would show what the end result I would like to achieve:

In the above images, the user has selected the Stonehenge Game Board from a slicer.  Stonehenge has played 7 games against Great Wall and won 1 and lost 6.  (the numbers I used in the mock up table are completely made up... just wanted to illustrate the end point)

I have added the raw data that I am using if that will help.https://1drv.ms/x/s!ArEg3qRFCqfOgtcWuizQ4n2XhxuwPw?e=VWfdMK 

I am trying to build this application as a way to learn Power BI.  Thank you to anyone who can give me suggestions/solutions as to how to solve this problem.

Kind regards,

Paul

[rajulshah]

Hello @paulvans182,

Please try the following measures:

Games Won = CALCULATE( COUNT(Game[Game ID]),Game[Results]="W")

Games Lost = CALCULATE( COUNT(Game[Game ID]),Game[Results]="L")

Games Played = COUNT(Game[Game ID])

Hope this helps.

[paulvans182]

Hi @rajulshah ,

Thanks for the response.  I do have those 3 measures in my model and have the resulting table which is fine for just a basic analysis of all games played, won and lost:

However, if I click on Stonehenge currently, it then just filters out all the other game boards.

Instead, I would like the table to show the overall results in terms of that specific board chosen.

In other words, if I select , Stonehenge, I would like to see how many games that board has played against the rest of the boards - and how many wins/losses it has.

If I filter the results manually in Excel to show only games that involve Stonehenge, I can see these 6 games have taken place:

Therefore, the desired output I am aiming for is to be able to show a table that shows that Stonehenge has played against Great Wall twice, and won both times but has never beaten Babylon:

I hope that makes sense? 

Kind regards,

[rajulshah]

@paulvans182,

Please try the following three measures:

Games Won = 
VAR GamesWon = 
CALCULATE( COUNT(Game[Game ID]),Game[Results]="W")
RETURN IF(ISBLANK(GamesWon),0,GamesWon)

Games Lost = 
VAR GamesLost = 
CALCULATE( COUNT(Game[Game ID]),Game[Results]="L")
RETURN IF(ISBLANK(GamesLost),0,GamesLost)

Games Played = IF(ISBLANK(COUNT(Game[Game ID])),0,COUNT(Game[Game ID]))

 Please keep the following measure as 'Visual Level Filter' on the visualization and filter it to '1'.

IsAllBlank = IF([Games Won]<>0 && [Games Played]<>0 && [Games Lost]<>0,1)

 Let me know if this didn't help.