cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bw70316
Helper V
Helper V

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

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:

 

20056882 Islanders75 Sabres 1 R16FinalO675 Sabres82 IslandersN/A
20056875 Sabres82 Islanders1  R16FinalO675 Sabres82 IslandersChampion
20056775 Sabres82 Islanders 1 R16FinalO582 Islanders75 SabresChampion
20056782 Islanders75 Sabres1  R16FinalO582 Islanders75 SabresN/A
20056682 Islanders75 Sabres1  R16FinalO475 Sabres82 IslandersN/A
20056675 Sabres82 Islanders 1 R16FinalO475 Sabres82 IslandersChampion
20056582 Islanders75 Sabres 1 R16FinalO375 Sabres82 IslandersN/A
20056575 Sabres82 Islanders1  R16FinalO375 Sabres82 IslandersChampion
20056475 Sabres82 Islanders1  R16FinalO282 Islanders75 SabresChampion
20056482 Islanders75 Sabres 1 R16FinalO282 Islanders75 SabresN/A
20056375 Sabres82 Islanders 1 R16FinalO182 Islanders75 SabresChampion
20056382 Islanders75 Sabres1  R16FinalO182 Islanders75 SabresN/A
200613096 Panthers06 France1  R16FinalO206 France96 PanthersN/A
200613006 France96 Panthers 1 R16FinalO206 France96 PanthersChampion
200613596 Panthers06 France 1 R16FinalO706 France96 PanthersN/A
200613506 France96 Panthers1  R16FinalO706 France96 PanthersChampion
20056975 Sabres82 Islanders1  R16FinalO782 Islanders75 SabresChampion
20056982 Islanders75 Sabres 1 R16FinalO782 Islanders75 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.  

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

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)

 

7.PNG

8.PNG

9.PNG

 

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

3 REPLIES 3
v-jayw-msft
Community Support
Community Support

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)

 

7.PNG

8.PNG

9.PNG

 

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

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

mahoneypat
Super User
Super User

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.