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
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors