Use If statement to assign label in new column when a threshold is met for that year?

[bw70316]

I am trying to assign a label to a specific row when a threshold is met for a given year. In this case, when the finals wins total =4, I want to declare that team the champion for the specific year. 

I tried this earlier and got this response: 

Measure = 
VAR __wins = CALCULATE ( SUM ( Table[Wins] ), ALLEXCEPT ( Table, Table[Team] ), Table[Round] = "Final" )
RETURN
IF ( __wins = 4, "Champion", "N/A" )

It yielded this result:

2005

68

82 Islanders

75 Sabres

1

R16

Final

O

6

75 Sabres

82 Islanders

N/A

2005

68

75 Sabres

82 Islanders

1

R16

Final

O

6

75 Sabres

82 Islanders

Champion

2005

67

75 Sabres

82 Islanders

1

R16

Final

O

5

82 Islanders

75 Sabres

Champion

2005

67

82 Islanders

75 Sabres

1

R16

Final

O

5

82 Islanders

75 Sabres

N/A

2005

66

82 Islanders

75 Sabres

1

R16

Final

O

4

75 Sabres

82 Islanders

N/A

2005

66

75 Sabres

82 Islanders

1

R16

Final

O

4

75 Sabres

82 Islanders

Champion

2005

65

82 Islanders

75 Sabres

1

R16

Final

O

3

75 Sabres

82 Islanders

N/A

2005

65

75 Sabres

82 Islanders

1

R16

Final

O

3

75 Sabres

82 Islanders

Champion

2005

64

75 Sabres

82 Islanders

1

R16

Final

O

2

82 Islanders

75 Sabres

Champion

2005

64

82 Islanders

75 Sabres

1

R16

Final

O

2

82 Islanders

75 Sabres

N/A

2005

63

75 Sabres

82 Islanders

1

R16

Final

O

1

82 Islanders

75 Sabres

Champion

2005

63

82 Islanders

75 Sabres

1

R16

Final

O

1

82 Islanders

75 Sabres

N/A

2006

130

96 Panthers

06 France

1

R16

Final

O

2

06 France

96 Panthers

N/A

2006

130

06 France

96 Panthers

1

R16

Final

O

2

06 France

96 Panthers

Champion

2006

135

96 Panthers

06 France

1

R16

Final

O

7

06 France

96 Panthers

N/A

2006

135

06 France

96 Panthers

1

R16

Final

O

7

06 France

96 Panthers

Champion

2005

69

75 Sabres

82 Islanders

1

R16

Final

O

7

82 Islanders

75 Sabres

Champion

2005

69

82 Islanders

75 Sabres

1

R16

Final

O

7

82 Islanders

75 Sabres

N/A

What I am hoping for is just one row that says Champion in 2005, one row that says champion in 2006. I tried doing Final Wins - Finals losses after creating Calculate Sum Filters on the Database[Win}/Database[Loss], but some teams played in multiple finals causing the results to be off.  

[v-jayw-msft · Accepted solution]

Hi @bw70316 ,

There's no column header in your table that i can't associate it with the formula.

Below is my sample data and formula. Check if it's what you want. If not, please show the expected out put to us.

Measure = 
var wins = CALCULATE(SUM('Table'[wins]),FILTER(ALLEXCEPT('Table','Table'[year],'Table'[team]),'Table'[round]="final"))
return
IF(wins=4,"Champion","N/A")

Measure 2 = IF([Measure]="Champion",1,0)

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[bw70316]

The above Dax is the right solution. Not sure how to change it. Sorry. 

[mahoneypat]

Try adding the Year column also inside your ALLEXCEPT().

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat