Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AnkitKukreja
Super User
Super User

Count Final Wins

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.

 

DateStageGamesResult
02-06-1991FIN1L
05-06-1991FIN2W
07-06-1991FIN3W
09-06-1991FIN4W
12-06-1991FIN5W
03-06-1992FIN1W
05-06-1992FIN2L
07-06-1992FIN3W
10-06-1992FIN4L
12-06-1992FIN5W
14-06-1992FIN6W
09-06-1993FIN1W
11-06-1993FIN2W
13-06-1993FIN3L
16-06-1993FIN4W
18-06-1993FIN5L
20-06-1993FIN6W
05-06-1996FIN1W
07-06-1996FIN2W
09-06-1996FIN3W
12-06-1996FIN4L
14-06-1996FIN5L
16-06-1996FIN6W
01-06-1997FIN1W
04-06-1997FIN2W
06-06-1997FIN3L
08-06-1997FIN4L
11-06-1997FIN5W
13-06-1997FIN6W
03-06-1998FIN1L
05-06-1998FIN2W
07-06-1998FIN3W
10-06-1998FIN4W
12-06-1998FIN5L
14-06-1998FIN6W

 

Thanks,

Ankit

If my response has successfully addressed your question or concern, I kindly request that you mark this post as resolved. Additionally, if you found my assistance helpful, a thumbs-up would be greatly appreciated.
Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904
1 ACCEPTED SOLUTION
jameszhang0805
Resolver IV
Resolver IV

Not sure if this is the result you want, but I didn't find the team information in your data source, so I created a calculated column Year for dimension.

 

#Win = 
VAR _Table =
    CALCULATETABLE (
        SUMMARIZE ( 'Table', 'Table'[Year], 'Table'[Games], 'Table'[Result] ),
        'Table'[Stage] = "FIN",
        'Table'[Result] = "W"
    )
VAR _GROUPBY =
    GROUPBY ( _Table, 'Table'[Year], "@Win", SUMX ( CURRENTGROUP (), 1 ) )
VAR _Filtered =
    FILTER ( _GROUPBY, [@Win] >= 4 )
RETURN
    COUNTROWS ( _Filtered )

 

 

jameszhang0805_4-1615609005620.png

 

View solution in original post

1 REPLY 1
jameszhang0805
Resolver IV
Resolver IV

Not sure if this is the result you want, but I didn't find the team information in your data source, so I created a calculated column Year for dimension.

 

#Win = 
VAR _Table =
    CALCULATETABLE (
        SUMMARIZE ( 'Table', 'Table'[Year], 'Table'[Games], 'Table'[Result] ),
        'Table'[Stage] = "FIN",
        'Table'[Result] = "W"
    )
VAR _GROUPBY =
    GROUPBY ( _Table, 'Table'[Year], "@Win", SUMX ( CURRENTGROUP (), 1 ) )
VAR _Filtered =
    FILTER ( _GROUPBY, [@Win] >= 4 )
RETURN
    COUNTROWS ( _Filtered )

 

 

jameszhang0805_4-1615609005620.png

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.