DAX determine who you lose the most to in games (Complex)

[joshuavd]

We play foosball at our company and play mostly adhoc games.  I would like to determine two things:

1. Who is your greatest ally? (The person you win the most with, in terms of count, when you play)
   1. Games[Type] = "Doubles"
   2. Foosey[Score] = 10 (this indicates a win)
   3. Foosey[Game]  = Same
   4. Count all games where the above is true and display the name of whoever has the most wins when they play with you - If you see below, in blue you can see it was the same game, and in red you can see that matt won a game when he played with Adam, so for matt he has +1 adam for the round and Adam has +1 matt.  This count would go for every game played and the highest scored name gets displayed as greatest ally
   5. 
2. Who is your greatest nemesis? (The person you have lost the most to)
   1. This is basically the exact opposite of above.  Except this also includes both doubles and singles games (for doubles, you lose to both players, so each player gets a count)

I cannot figure out if this is possible or if I have to create another intermediary table to calculate this stuff... But some direction would be great.

[Sean]

If you have the data table on the left - what do you expects the results to be or to look like?

Something like the table on the right?

[joshuavd]

Not quite, I didn't explain very well.  I would expect to get this:

Each of those names is calculated based on the amount of games you've won  with the other person or based on the count of games you have lost against someone.

[OwenAuger · Accepted solution]

Hi there,

Interesting problem 

I had a play with this and here is a sample solution in a PBIX file with source spreadsheet.

https://www.dropbox.com/s/szg1uesipaxa15x/Foosey%20Greatest%20Ally%20and%20Nemesis.pbix?dl=0

https://www.dropbox.com/s/wodxj443py5xdk0/FooseyData.xlsx?dl=0

Seems to work but please test it out 🙂

I think this could be possibly modelled similarly to  'basket analysis' (http://www.daxpatterns.com/basket-analysis/) but I have not tried this.

• I've assumed Score = 10 is a win and Score = 5 is a loss - is that how your scoring works?
• I have defined measures just using columns from the Foosey table.
  • The Games table proved unnecessary for identifying whether players were on the same side or not in a particular game, since this is evident from their Scores.
  • Also it wasn't necessary to classify games a singles or doubles. Players are simply counted if they appear on the same or opposite side in a particular game (using the Scores).
• The Greatest Ally measure filters down to players (who are not the currently selected player(s)) and who scored the same as the selected player(s) on the same game, with score = 10, then counts occurrences and chooses the most frequent.
• The Nemesis measure filters down to players (who are not the currently selected player(s)) who had score = 10 in games where the selected player(s) had score = 5, then again counts occurrences and chooses the most frequent.

Greatest Ally = 
// Note: Ties are broken by selecting the first Player alphabetically using FIRSTNONBLANK
CALCULATE (
    FIRSTNONBLANK ( Foosey[Player], 0 ),
    TOPN (
        1,
        CALCULATETABLE (
            ADDCOLUMNS (
                VALUES ( Foosey[Player] ),
                "Count", CALCULATE ( COUNTROWS ( Foosey ) )
            ),
            SUMMARIZE ( Foosey, Foosey[Game], Foosey[Score] ),
            FILTER (
                ALL ( Foosey[Player] ),
                NOT ( CONTAINS ( VALUES ( Foosey[Player] ), Foosey[Player], Foosey[Player] ) )
            ),
            Foosey[Score] = 10
        ),
        [Count]
    )
)

Nemesis = 
// Note: Ties are broken by selecting the first Player alphabetically using FIRSTNONBLANK
CALCULATE (
    FIRSTNONBLANK ( Foosey[Player], 0 ),
    TOPN (
        1,
        CALCULATETABLE (
            ADDCOLUMNS (
                VALUES ( Foosey[Player] ),
                "Count", CALCULATE ( COUNTROWS ( Foosey ) )
            ),
            CALCULATETABLE ( VALUES ( Foosey[Game] ), Foosey[Score] = 5 ),
            FILTER (
                ALL ( Foosey[Player] ),
                NOT ( CONTAINS ( VALUES ( Foosey[Player] ), Foosey[Player], Foosey[Player] ) )
            ),
            Foosey[Score] = 10
        ),
        [Count]
    )
)

Owen

[Sean]

@OwenAuger Great job - seems ot work with the sample I had created!

[OwenAuger]

Glad it's working as intended 🙂

I forgot to mention that I wrote those measures to handle the Greatest Ally or Nemesis of a group as well as an individual.

However haven't tested that specifically.

e.g. If you select two people, at a total level, Greatest Ally should evaluate to the person who appears most often as the ally of either of the two people selected.

All the best,

Owen

[joshuavd]

This is fantastic!  I would have never of thought to do it via a measure as opposed to a table.  I plugged it in and it works great!  Now it will take me the next week to go through and understand exactly what you did .  Only one change was needed, a score of less than 10 indicates a loss, so I made that adjustment in the formula but other than that it does exactly what I need!  I really appreciate the help!

[Sean]

So if you have the data table on the left - what do you expect the result to be? Something like the table on the right?

[Sean]

So if you have the data table on the left - what do you expect the result to be? Something like the table on the right?