Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
The above Dax is the right solution. Not sure how to change it. Sorry.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |