Count Final Wins

DAX DAX Calculations
1

Hi All,

I want to count the wins from the dataset below. The stage Fin refers to finals and there are 7 games in each final, so the games 1-5 in 1991 finals and 1-6 in 1992 is final games for that period, and I need to find if the team has won more than or equal to 4 times out of those games played. Result gives me the status Win (W) and Loss (L).

So ideally, in 1991 team has lost game 1 and won other 4 games (2-5), so that gives me count 1 and so on.

My stage also consists of other games (Qtrfin, EC1, EC2) as well, but I’m concerned about the Fin stage.

Date Stage Games Result
02-06-1991 FIN 1 L
05-06-1991 FIN 2 W
07-06-1991 FIN 3 W
09-06-1991 FIN 4 W
12-06-1991 FIN 5 W
03-06-1992 FIN 1 W
05-06-1992 FIN 2 L
07-06-1992 FIN 3 W
10-06-1992 FIN 4 L
12-06-1992 FIN 5 W
14-06-1992 FIN 6 W
09-06-1993 FIN 1 W
11-06-1993 FIN 2 W
13-06-1993 FIN 3 L
16-06-1993 FIN 4 W
18-06-1993 FIN 5 L
20-06-1993 FIN 6 W
05-06-1996 FIN 1 W
07-06-1996 FIN 2 W
09-06-1996 FIN 3 W
12-06-1996 FIN 4 L
14-06-1996 FIN 5 L
16-06-1996 FIN 6 W
01-06-1997 FIN 1 W
04-06-1997 FIN 2 W
06-06-1997 FIN 3 L
08-06-1997 FIN 4 L
11-06-1997 FIN 5 W
13-06-1997 FIN 6 W
03-06-1998 FIN 1 L
05-06-1998 FIN 2 W
07-06-1998 FIN 3 W
10-06-1998 FIN 4 W
12-06-1998 FIN 5 L
14-06-1998 FIN 6 W

Thanks,
Ankit

2

Hi @kkrj.ankit

Are you looking for something like this.

image_2021-03-12_105837

Win&Lose.pbix (26.5 KB)

1 Like
3

Hi @kkrj.ankit,

See if this meets your requirement.

image

Count Won.pbix (62.6 KB)
I hope this is helpful.

1 Like
4

@kkrj.ankit,

LOL. @jbressan, @Melissa and I all must have jumped on this one at close to the same time. For the sake of completeness, here’s another solution - most of the work is done by the Power Query GROUP BY function, then this DAX measure at the end to tally the card:

Count Series Wins = 

VAR vTable =
SUMMARIZE(
    'Table',
    'Table'[Year],
    'Table'[Series Outcome]
)

VAR Result = 
CALCULATE(
    COUNTROWS( vTable ),
    FILTER( vTable, 'Table'[Series Outcome] = "W")
)

RETURN Result

image

I hope this is helpful. Full solution file attached.

  • Brian

eDNA Forum - Series Outcome Solution.pbix (18.2 KB)

1 Like
5

Hi @jbressan, @Melissa , @BrianJ
You guys are awesome. There is only one place on earth where you can expect quick solutions and that too not 1 or 2 but many more.

Best approach for me is what Melissa has shared. But, I would love to mark all three posts as a solution.

@EnterpriseDNA team, please add a functionality to mark multiple posts as a solution.

Happy Friday guys :slight_smile:

Thanks,
Ankit

2 Likes
6

Hi @Melissa

One question here, why have you used group by in Query editor, when everything is handled by your dax?

Thanks,
Ankit

7

@Melissa,

That’s a beautiful piece of DAX coding. One question – any particular reason you used GENERATE (VALUES)) rather than SUMMARIZE? I always tend to use the latter, but I’ve noticed recently you using the former a lot more and wondered if there’s a tangible difference and benefit to doing so?

Thanks.

– Brian

1 Like
8

That is usually my default approach, something similair to what @BrianJ shared.
But finally decided to go with a 100% DAX solution, using PQ result just for validation.

1 Like
9

:thinking: Not really, I use both. I should probably start working with DAX Studio and find out…

10

Hi Ankit,

Thanks for the suggestion. It would be great to acknowledge all solutions and expert efforts placed on solving problems.

However, there seems to be no option to choose multiple solutions to a post. You may award the post with a like to give credit to the poster.

Best,
Enterprise DNA Team

11

@kkrj.ankit,

It is a really nice thought, but all’s well that ends well. It was a really interesting question, and I actually used it as the basis for a video coming out next week, plus I got to dig back into the 1990s Chicago Bulls data… :grinning:

  • Brian
1 Like