topic Use If statement to assign label in new column when a threshold is met for that year? in Desktop
https://community.powerbi.com/t5/Desktop/Use-If-statement-to-assign-label-in-new-column-when-a-threshold/m-p/1130085#M515112
<P>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. </P><P> </P><P>I tried this earlier and got this response: </P><P> </P><PRE>Measure =
VAR __wins = CALCULATE ( SUM ( Table[Wins] ), ALLEXCEPT ( Table, Table[Team] ), Table[Round] = "Final" )
RETURN
IF ( __wins = 4, "Champion", "N/A" )</PRE><P> </P><P>It yielded this result:</P><P> </P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>2005</TD><TD>68</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD> </TD><TD>1</TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>6</TD><TD>75 Sabres</TD><TD>82 Islanders</TD><TD>N/A</TD></TR><TR><TD>2005</TD><TD>68</TD><TD>75 Sabres</TD><TD>82 Islanders</TD><TD>1</TD><TD> </TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>6</TD><TD>75 Sabres</TD><TD>82 Islanders</TD><TD>Champion</TD></TR><TR><TD>2005</TD><TD>67</TD><TD>75 Sabres</TD><TD>82 Islanders</TD><TD> </TD><TD>1</TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>5</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD>Champion</TD></TR><TR><TD>2005</TD><TD>67</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD>1</TD><TD> </TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>5</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD>N/A</TD></TR><TR><TD>2005</TD><TD>66</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD>1</TD><TD> </TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>4</TD><TD>75 Sabres</TD><TD>82 Islanders</TD><TD>N/A</TD></TR><TR><TD>2005</TD><TD>66</TD><TD>75 Sabres</TD><TD>82 Islanders</TD><TD> </TD><TD>1</TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>4</TD><TD>75 Sabres</TD><TD>82 Islanders</TD><TD>Champion</TD></TR><TR><TD>2005</TD><TD>65</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD> </TD><TD>1</TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>3</TD><TD>75 Sabres</TD><TD>82 Islanders</TD><TD>N/A</TD></TR><TR><TD>2005</TD><TD>65</TD><TD>75 Sabres</TD><TD>82 Islanders</TD><TD>1</TD><TD> </TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>3</TD><TD>75 Sabres</TD><TD>82 Islanders</TD><TD>Champion</TD></TR><TR><TD>2005</TD><TD>64</TD><TD>75 Sabres</TD><TD>82 Islanders</TD><TD>1</TD><TD> </TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>2</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD>Champion</TD></TR><TR><TD>2005</TD><TD>64</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD> </TD><TD>1</TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>2</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD>N/A</TD></TR><TR><TD>2005</TD><TD>63</TD><TD>75 Sabres</TD><TD>82 Islanders</TD><TD> </TD><TD>1</TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>1</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD>Champion</TD></TR><TR><TD>2005</TD><TD>63</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD>1</TD><TD> </TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>1</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD>N/A</TD></TR><TR><TD>2006</TD><TD>130</TD><TD>96 Panthers</TD><TD>06 France</TD><TD>1</TD><TD> </TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>2</TD><TD>06 France</TD><TD>96 Panthers</TD><TD>N/A</TD></TR><TR><TD>2006</TD><TD>130</TD><TD>06 France</TD><TD>96 Panthers</TD><TD> </TD><TD>1</TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>2</TD><TD>06 France</TD><TD>96 Panthers</TD><TD>Champion</TD></TR><TR><TD>2006</TD><TD>135</TD><TD>96 Panthers</TD><TD>06 France</TD><TD> </TD><TD>1</TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>7</TD><TD>06 France</TD><TD>96 Panthers</TD><TD>N/A</TD></TR><TR><TD>2006</TD><TD>135</TD><TD>06 France</TD><TD>96 Panthers</TD><TD>1</TD><TD> </TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>7</TD><TD>06 France</TD><TD>96 Panthers</TD><TD>Champion</TD></TR><TR><TD>2005</TD><TD>69</TD><TD>75 Sabres</TD><TD>82 Islanders</TD><TD>1</TD><TD> </TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>7</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD>Champion</TD></TR><TR><TD>2005</TD><TD>69</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD> </TD><TD>1</TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>7</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD><P>N/A</P></TD></TR></TBODY></TABLE><P> </P><P> </P><P>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. </P>Fri, 29 May 2020 19:40:59 GMTbw703162020-05-29T19:40:59ZUse If statement to assign label in new column when a threshold is met for that year?
https://community.powerbi.com/t5/Desktop/Use-If-statement-to-assign-label-in-new-column-when-a-threshold/m-p/1130085#M515112
<P>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. </P><P> </P><P>I tried this earlier and got this response: </P><P> </P><PRE>Measure =
VAR __wins = CALCULATE ( SUM ( Table[Wins] ), ALLEXCEPT ( Table, Table[Team] ), Table[Round] = "Final" )
RETURN
IF ( __wins = 4, "Champion", "N/A" )</PRE><P> </P><P>It yielded this result:</P><P> </P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>2005</TD><TD>68</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD> </TD><TD>1</TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>6</TD><TD>75 Sabres</TD><TD>82 Islanders</TD><TD>N/A</TD></TR><TR><TD>2005</TD><TD>68</TD><TD>75 Sabres</TD><TD>82 Islanders</TD><TD>1</TD><TD> </TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>6</TD><TD>75 Sabres</TD><TD>82 Islanders</TD><TD>Champion</TD></TR><TR><TD>2005</TD><TD>67</TD><TD>75 Sabres</TD><TD>82 Islanders</TD><TD> </TD><TD>1</TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>5</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD>Champion</TD></TR><TR><TD>2005</TD><TD>67</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD>1</TD><TD> </TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>5</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD>N/A</TD></TR><TR><TD>2005</TD><TD>66</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD>1</TD><TD> </TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>4</TD><TD>75 Sabres</TD><TD>82 Islanders</TD><TD>N/A</TD></TR><TR><TD>2005</TD><TD>66</TD><TD>75 Sabres</TD><TD>82 Islanders</TD><TD> </TD><TD>1</TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>4</TD><TD>75 Sabres</TD><TD>82 Islanders</TD><TD>Champion</TD></TR><TR><TD>2005</TD><TD>65</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD> </TD><TD>1</TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>3</TD><TD>75 Sabres</TD><TD>82 Islanders</TD><TD>N/A</TD></TR><TR><TD>2005</TD><TD>65</TD><TD>75 Sabres</TD><TD>82 Islanders</TD><TD>1</TD><TD> </TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>3</TD><TD>75 Sabres</TD><TD>82 Islanders</TD><TD>Champion</TD></TR><TR><TD>2005</TD><TD>64</TD><TD>75 Sabres</TD><TD>82 Islanders</TD><TD>1</TD><TD> </TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>2</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD>Champion</TD></TR><TR><TD>2005</TD><TD>64</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD> </TD><TD>1</TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>2</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD>N/A</TD></TR><TR><TD>2005</TD><TD>63</TD><TD>75 Sabres</TD><TD>82 Islanders</TD><TD> </TD><TD>1</TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>1</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD>Champion</TD></TR><TR><TD>2005</TD><TD>63</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD>1</TD><TD> </TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>1</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD>N/A</TD></TR><TR><TD>2006</TD><TD>130</TD><TD>96 Panthers</TD><TD>06 France</TD><TD>1</TD><TD> </TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>2</TD><TD>06 France</TD><TD>96 Panthers</TD><TD>N/A</TD></TR><TR><TD>2006</TD><TD>130</TD><TD>06 France</TD><TD>96 Panthers</TD><TD> </TD><TD>1</TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>2</TD><TD>06 France</TD><TD>96 Panthers</TD><TD>Champion</TD></TR><TR><TD>2006</TD><TD>135</TD><TD>96 Panthers</TD><TD>06 France</TD><TD> </TD><TD>1</TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>7</TD><TD>06 France</TD><TD>96 Panthers</TD><TD>N/A</TD></TR><TR><TD>2006</TD><TD>135</TD><TD>06 France</TD><TD>96 Panthers</TD><TD>1</TD><TD> </TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>7</TD><TD>06 France</TD><TD>96 Panthers</TD><TD>Champion</TD></TR><TR><TD>2005</TD><TD>69</TD><TD>75 Sabres</TD><TD>82 Islanders</TD><TD>1</TD><TD> </TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>7</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD>Champion</TD></TR><TR><TD>2005</TD><TD>69</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD> </TD><TD>1</TD><TD> </TD><TD>R16</TD><TD>Final</TD><TD>O</TD><TD>7</TD><TD>82 Islanders</TD><TD>75 Sabres</TD><TD><P>N/A</P></TD></TR></TBODY></TABLE><P> </P><P> </P><P>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. </P>Fri, 29 May 2020 19:40:59 GMThttps://community.powerbi.com/t5/Desktop/Use-If-statement-to-assign-label-in-new-column-when-a-threshold/m-p/1130085#M515112bw703162020-05-29T19:40:59ZRe: Use If statement to assign label in new column when a threshold is met for that year?
https://community.powerbi.com/t5/Desktop/Use-If-statement-to-assign-label-in-new-column-when-a-threshold/m-p/1130403#M515198
<P>Try adding the Year column also inside your ALLEXCEPT().</P><P> </P><P>If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.</P><P>Regards,</P><P>Pat</P>Sat, 30 May 2020 03:31:51 GMThttps://community.powerbi.com/t5/Desktop/Use-If-statement-to-assign-label-in-new-column-when-a-threshold/m-p/1130403#M515198mahoneypat2020-05-30T03:31:51ZRe: Use If statement to assign label in new column when a threshold is met for that year?
https://community.powerbi.com/t5/Desktop/Use-If-statement-to-assign-label-in-new-column-when-a-threshold/m-p/1135553#M516771
<P>Hi <LI-USER uid="71837"></LI-USER> ,</P>
<P> </P>
<P>There's no column header in your table that i can't associate it with the formula.</P>
<P>Below is my sample data and formula. Check if it's what you want. If not, please show the expected out put to us.</P>
<P> </P>
<LI-CODE lang="markup">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)</LI-CODE>
<P> </P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="7.PNG" style="width: 283px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/286705i933C565A0E5E4712/image-size/medium?v=v2&px=400" role="button" title="7.PNG" alt="7.PNG" /></span></P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="8.PNG" style="width: 293px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/286710i35A4686DEC9AF841/image-size/medium?v=v2&px=400" role="button" title="8.PNG" alt="8.PNG" /></span></P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="9.PNG" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/286711iE6568A551AAD18EE/image-size/medium?v=v2&px=400" role="button" title="9.PNG" alt="9.PNG" /></span></P>
<P> </P>
<P>Best Regards,</P>
<P>Jay</P>
<P>Community Support Team _ Jay Wang</P>
<P>If this post <STRONG>helps</STRONG>, then please consider <STRONG><EM>Accept it as the solution</EM></STRONG> to help the other members find it more quickly.</P>Tue, 02 Jun 2020 09:32:52 GMThttps://community.powerbi.com/t5/Desktop/Use-If-statement-to-assign-label-in-new-column-when-a-threshold/m-p/1135553#M516771v-jayw-msft2020-06-02T09:32:52ZRe: Use If statement to assign label in new column when a threshold is met for that year?
https://community.powerbi.com/t5/Desktop/Use-If-statement-to-assign-label-in-new-column-when-a-threshold/m-p/1148961#M521543
<P>The above Dax is the right solution. Not sure how to change it. Sorry. </P>Tue, 09 Jun 2020 14:08:43 GMThttps://community.powerbi.com/t5/Desktop/Use-If-statement-to-assign-label-in-new-column-when-a-threshold/m-p/1148961#M521543bw703162020-06-09T14:08:43Z