topic Re: Replacing SUMMARIZECOLUMNS with SUMMARIZE to get measure that works in Desktop to work in Fabric in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Replacing-SUMMARIZECOLUMNS-with-SUMMARIZE-to-get-measure-that/m-p/4029648#M159274 <P>I managed to resolve this with the following code:</P><P>I added a new column</P><DIV><PRE><SPAN>Quote Last Row excl Negl</SPAN><SPAN>.</SPAN><SPAN> =</SPAN><BR /><SPAN>VAR</SPAN> <SPAN>lastindex</SPAN><SPAN> = </SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN><SPAN>MAXX</SPAN><SPAN>(</SPAN><SPAN>'Combined Data'</SPAN><SPAN>, </SPAN><SPAN>'Combined Data'</SPAN><SPAN>[Row Index]</SPAN><SPAN>-</SPAN><SPAN>IF</SPAN><SPAN>(</SPAN><SPAN>'Combined Data'</SPAN><SPAN>[Negligible]</SPAN><SPAN>,</SPAN><SPAN>100</SPAN><SPAN>,</SPAN><SPAN>0</SPAN><SPAN>)), </SPAN><SPAN>ALLEXCEPT</SPAN><SPAN>(</SPAN><SPAN>'Combined Data'</SPAN><SPAN>, </SPAN><SPAN>'Combined Data'</SPAN><SPAN>[Quote ID]</SPAN><SPAN>, </SPAN><SPAN>'Combined Data'</SPAN><SPAN>[Report Type]</SPAN><SPAN>, </SPAN><SPAN>'Combined Data'</SPAN><SPAN>[FiscalYear]</SPAN><SPAN>))</SPAN><BR /><SPAN>RETURN</SPAN> <SPAN>'Combined Data'</SPAN><SPAN>[Row Index]</SPAN><SPAN> = </SPAN><SPAN>lastindex</SPAN></PRE></DIV><P>then</P><DIV><PRE><SPAN>Latest Quote Revenue USD test2 =</SPAN><BR /><SPAN>VAR</SPAN> <SPAN>includeclosed</SPAN><SPAN> = </SPAN><SPAN>SELECTEDVALUE</SPAN><SPAN>(</SPAN><SPAN>'Closed Business'</SPAN><SPAN>[Include]</SPAN><SPAN>, </SPAN><SPAN>TRUE</SPAN><SPAN>)</SPAN><BR /><SPAN>VAR</SPAN> <SPAN>summ</SPAN><SPAN> = </SPAN><SPAN>ADDCOLUMNS</SPAN><SPAN>(</SPAN><SPAN>SUMMARIZE</SPAN><SPAN>(</SPAN><SPAN>'Combined Data'</SPAN><SPAN>, </SPAN><SPAN>'Combined Data'</SPAN><SPAN>[Company]</SPAN><SPAN>, </SPAN><SPAN>'Calendar'</SPAN><SPAN>[Year]</SPAN><SPAN>, </SPAN><SPAN>'Combined Data'</SPAN><SPAN>[Quote Year]</SPAN><SPAN>),</SPAN><SPAN>"val"</SPAN><SPAN>, </SPAN><BR /><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN><SPAN>MAX</SPAN><SPAN>(</SPAN><SPAN>'Combined Data'</SPAN><SPAN>[Quote Estimated Revenue]</SPAN><SPAN>), </SPAN><SPAN>'Combined Data'</SPAN><SPAN>[Quote Last Row excl Negl.]</SPAN><SPAN> &nbsp;&amp;&amp; (</SPAN><SPAN>'Combined Data'</SPAN><SPAN>[Quarter Start Date]</SPAN><SPAN> &lt; </SPAN><SPAN>'Combined Data'</SPAN><SPAN>[Quote Termination Date]</SPAN><SPAN> || (</SPAN><SPAN>'Combined Data'</SPAN><SPAN>[Negligible]</SPAN><SPAN> = </SPAN><SPAN>FALSE</SPAN><SPAN> &amp;&amp; </SPAN><SPAN>includeclosed</SPAN><SPAN> = </SPAN><SPAN>TRUE</SPAN><SPAN>))))</SPAN><BR /><SPAN>&nbsp; &nbsp; </SPAN><SPAN>RETURN</SPAN> <SPAN>SUMX</SPAN><SPAN>(</SPAN><SPAN>summ</SPAN><SPAN>, </SPAN><SPAN>[val]</SPAN><SPAN>)</SPAN></PRE></DIV> Mon, 08 Jul 2024 08:09:31 GMT MBZA 2024-07-08T08:09:31Z Replacing SUMMARIZECOLUMNS with SUMMARIZE to get measure that works in Desktop to work in Fabric https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Replacing-SUMMARIZECOLUMNS-with-SUMMARIZE-to-get-measure-that/m-p/4018430#M158485 <P>I'm having an issue with a measure that works fine in Desktop but fails after publishing, I think because of the context filter issues with SUMMARIZECOLUMNS.</P><P>&nbsp;</P><P>I'm using a dataset called "Combined Data" that looks like the following:</P><TABLE><TBODY><TR><TD>FiscalYear</TD><TD>Fiscal Quarter</TD><TD>Client</TD><TD>Deal</TD><TD>Quote Year</TD><TD>Quote Effective Date</TD><TD>Quote Termination Date</TD><TD>Quarter Start Date</TD><TD>Quarter End Date</TD><TD>Actual Revenue</TD><TD>Quote Estimated Revenue</TD><TD>Row Index</TD><TD>Quote Last Row</TD><TD>Negligible</TD></TR><TR><TD>2021</TD><TD>2021-Q1</TD><TD>A</TD><TD>A</TD><TD>2010</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>2021/01/01</TD><TD>2021/03/31</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>75</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2021</TD><TD>2021-Q1</TD><TD>A</TD><TD>B</TD><TD>2011</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>2021/01/01</TD><TD>2021/03/31</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>76</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2021</TD><TD>2021-Q1</TD><TD>A</TD><TD>C</TD><TD>2015</TD><TD>2015/12/31</TD><TD>2016/12/30</TD><TD>2021/01/01</TD><TD>2021/03/31</TD><TD>&nbsp;</TD><TD>150297.5</TD><TD>44</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2021</TD><TD>2021-Q1</TD><TD>A</TD><TD>D</TD><TD>2013</TD><TD>2013/12/31</TD><TD>2014/12/30</TD><TD>2021/01/01</TD><TD>2021/03/31</TD><TD>&nbsp;</TD><TD>267745.98</TD><TD>52</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2021</TD><TD>2021-Q1</TD><TD>A</TD><TD>E</TD><TD>2012</TD><TD>2012/12/31</TD><TD>2013/12/30</TD><TD>2021/01/01</TD><TD>2021/03/31</TD><TD>&nbsp;</TD><TD>267745.98</TD><TD>60</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2021</TD><TD>2021-Q2</TD><TD>A</TD><TD>B</TD><TD>2011</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>2021/04/01</TD><TD>2021/06/30</TD><TD>7.93</TD><TD>&nbsp;</TD><TD>73</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2021</TD><TD>2021-Q2</TD><TD>A</TD><TD>A</TD><TD>2010</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>2021/04/01</TD><TD>2021/06/30</TD><TD>17.48</TD><TD>&nbsp;</TD><TD>74</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2021</TD><TD>2021-Q2</TD><TD>A</TD><TD>C</TD><TD>2015</TD><TD>2015/12/31</TD><TD>2016/12/30</TD><TD>2021/04/01</TD><TD>2021/06/30</TD><TD>17.53</TD><TD>150297.5</TD><TD>43</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2021</TD><TD>2021-Q2</TD><TD>A</TD><TD>D</TD><TD>2013</TD><TD>2013/12/31</TD><TD>2014/12/30</TD><TD>2021/04/01</TD><TD>2021/06/30</TD><TD>688.39</TD><TD>267745.98</TD><TD>51</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2021</TD><TD>2021-Q2</TD><TD>A</TD><TD>E</TD><TD>2012</TD><TD>2012/12/31</TD><TD>2013/12/30</TD><TD>2021/04/01</TD><TD>2021/06/30</TD><TD>26.51</TD><TD>267745.98</TD><TD>59</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2021</TD><TD>2021-Q3</TD><TD>A</TD><TD>A</TD><TD>2010</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>2021/07/01</TD><TD>2021/09/30</TD><TD>17.14</TD><TD>&nbsp;</TD><TD>71</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2021</TD><TD>2021-Q3</TD><TD>A</TD><TD>B</TD><TD>2011</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>2021/07/01</TD><TD>2021/09/30</TD><TD>7.77</TD><TD>&nbsp;</TD><TD>72</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2021</TD><TD>2021-Q3</TD><TD>A</TD><TD>C</TD><TD>2015</TD><TD>2015/12/31</TD><TD>2016/12/30</TD><TD>2021/07/01</TD><TD>2021/09/30</TD><TD>17.19</TD><TD>150297.5</TD><TD>42</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2021</TD><TD>2021-Q3</TD><TD>A</TD><TD>D</TD><TD>2013</TD><TD>2013/12/31</TD><TD>2014/12/30</TD><TD>2021/07/01</TD><TD>2021/09/30</TD><TD>675.21</TD><TD>267745.98</TD><TD>50</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2021</TD><TD>2021-Q3</TD><TD>A</TD><TD>E</TD><TD>2012</TD><TD>2012/12/31</TD><TD>2013/12/30</TD><TD>2021/07/01</TD><TD>2021/09/30</TD><TD>26.03</TD><TD>267745.98</TD><TD>58</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2021</TD><TD>2021-Q4</TD><TD>A</TD><TD>B</TD><TD>2011</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>2021/10/01</TD><TD>2021/12/31</TD><TD>7.54</TD><TD>&nbsp;</TD><TD>69</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2021</TD><TD>2021-Q4</TD><TD>A</TD><TD>A</TD><TD>2010</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>2021/10/01</TD><TD>2021/12/31</TD><TD>16.6</TD><TD>&nbsp;</TD><TD>70</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2021</TD><TD>2021-Q4</TD><TD>A</TD><TD>C</TD><TD>2015</TD><TD>2015/12/31</TD><TD>2016/12/30</TD><TD>2021/10/01</TD><TD>2021/12/31</TD><TD>16.63</TD><TD>150297.5</TD><TD>41</TD><TD>True</TD><TD>False</TD></TR><TR><TD>2021</TD><TD>2021-Q4</TD><TD>A</TD><TD>D</TD><TD>2013</TD><TD>2013/12/31</TD><TD>2014/12/30</TD><TD>2021/10/01</TD><TD>2021/12/31</TD><TD>654</TD><TD>267745.98</TD><TD>49</TD><TD>True</TD><TD>False</TD></TR><TR><TD>2021</TD><TD>2021-Q4</TD><TD>A</TD><TD>E</TD><TD>2012</TD><TD>2012/12/31</TD><TD>2013/12/30</TD><TD>2021/10/01</TD><TD>2021/12/31</TD><TD>25.2</TD><TD>267745.98</TD><TD>57</TD><TD>True</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q1</TD><TD>A</TD><TD>B</TD><TD>2011</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>2022/01/01</TD><TD>2022/03/31</TD><TD>6.6</TD><TD>&nbsp;</TD><TD>67</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q1</TD><TD>A</TD><TD>A</TD><TD>2010</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>2022/01/01</TD><TD>2022/03/31</TD><TD>14.53</TD><TD>&nbsp;</TD><TD>68</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q1</TD><TD>A</TD><TD>C</TD><TD>2015</TD><TD>2015/12/31</TD><TD>2016/12/30</TD><TD>2022/01/01</TD><TD>2022/03/31</TD><TD>14.56</TD><TD>150297.5</TD><TD>40</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q1</TD><TD>A</TD><TD>D</TD><TD>2013</TD><TD>2013/12/31</TD><TD>2014/12/30</TD><TD>2022/01/01</TD><TD>2022/03/31</TD><TD>571.99</TD><TD>267745.98</TD><TD>48</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q1</TD><TD>A</TD><TD>E</TD><TD>2012</TD><TD>2012/12/31</TD><TD>2013/12/30</TD><TD>2022/01/01</TD><TD>2022/03/31</TD><TD>22.05</TD><TD>267745.98</TD><TD>56</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q2</TD><TD>A</TD><TD>B</TD><TD>2011</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>2022/04/01</TD><TD>2022/06/30</TD><TD>5.77</TD><TD>&nbsp;</TD><TD>65</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q2</TD><TD>A</TD><TD>C</TD><TD>2015</TD><TD>2015/12/31</TD><TD>2016/12/30</TD><TD>2022/04/01</TD><TD>2022/06/30</TD><TD>12.78</TD><TD>150297.5</TD><TD>39</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q2</TD><TD>A</TD><TD>A</TD><TD>2010</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>2022/04/01</TD><TD>2022/06/30</TD><TD>-1.4988E-15</TD><TD>&nbsp;</TD><TD>66</TD><TD>False</TD><TD>True</TD></TR><TR><TD>2022</TD><TD>2022-Q2</TD><TD>A</TD><TD>E</TD><TD>2012</TD><TD>2012/12/31</TD><TD>2013/12/30</TD><TD>2022/04/01</TD><TD>2022/06/30</TD><TD>-1.71</TD><TD>267745.98</TD><TD>55</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q2</TD><TD>A</TD><TD>D</TD><TD>2013</TD><TD>2013/12/31</TD><TD>2014/12/30</TD><TD>2022/04/01</TD><TD>2022/06/30</TD><TD>-44.35</TD><TD>267745.98</TD><TD>47</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q3</TD><TD>A</TD><TD>B</TD><TD>2011</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>2022/07/01</TD><TD>2022/09/30</TD><TD>5.46</TD><TD>&nbsp;</TD><TD>63</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q3</TD><TD>A</TD><TD>C</TD><TD>2015</TD><TD>2015/12/31</TD><TD>2016/12/30</TD><TD>2022/07/01</TD><TD>2022/09/30</TD><TD>12.08</TD><TD>150297.5</TD><TD>38</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q3</TD><TD>A</TD><TD>A</TD><TD>2010</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>2022/07/01</TD><TD>2022/09/30</TD><TD>-4.85723E-17</TD><TD>&nbsp;</TD><TD>64</TD><TD>False</TD><TD>True</TD></TR><TR><TD>2022</TD><TD>2022-Q3</TD><TD>A</TD><TD>E</TD><TD>2012</TD><TD>2012/12/31</TD><TD>2013/12/30</TD><TD>2022/07/01</TD><TD>2022/09/30</TD><TD>-1.78</TD><TD>267745.98</TD><TD>54</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q3</TD><TD>A</TD><TD>D</TD><TD>2013</TD><TD>2013/12/31</TD><TD>2014/12/30</TD><TD>2022/07/01</TD><TD>2022/09/30</TD><TD>-46.16</TD><TD>267745.98</TD><TD>46</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q4</TD><TD>A</TD><TD>B</TD><TD>2011</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>2022/10/01</TD><TD>2022/12/31</TD><TD>5.54</TD><TD>&nbsp;</TD><TD>61</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q4</TD><TD>A</TD><TD>C</TD><TD>2015</TD><TD>2015/12/31</TD><TD>2016/12/30</TD><TD>2022/10/01</TD><TD>2022/12/31</TD><TD>12.26</TD><TD>150297.5</TD><TD>37</TD><TD>True</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q4</TD><TD>A</TD><TD>A</TD><TD>2010</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>2022/10/01</TD><TD>2022/12/31</TD><TD>1.51268E-15</TD><TD>&nbsp;</TD><TD>62</TD><TD>False</TD><TD>True</TD></TR><TR><TD>2022</TD><TD>2022-Q4</TD><TD>A</TD><TD>E</TD><TD>2012</TD><TD>2012/12/31</TD><TD>2013/12/30</TD><TD>2022/10/01</TD><TD>2022/12/31</TD><TD>-1.87</TD><TD>267745.98</TD><TD>53</TD><TD>True</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q4</TD><TD>A</TD><TD>D</TD><TD>2013</TD><TD>2013/12/31</TD><TD>2014/12/30</TD><TD>2022/10/01</TD><TD>2022/12/31</TD><TD>-48.75</TD><TD>267745.98</TD><TD>45</TD><TD>True</TD><TD>False</TD></TR><TR><TD>2021</TD><TD>2021-Q1</TD><TD>B</TD><TD>F</TD><TD>2011</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>2021/01/01</TD><TD>2021/03/31</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>36</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2021</TD><TD>2021-Q2</TD><TD>B</TD><TD>F</TD><TD>2011</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>2021/04/01</TD><TD>2021/06/30</TD><TD>0.33</TD><TD>&nbsp;</TD><TD>35</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2021</TD><TD>2021-Q3</TD><TD>B</TD><TD>F</TD><TD>2011</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>2021/07/01</TD><TD>2021/09/30</TD><TD>4.68</TD><TD>&nbsp;</TD><TD>34</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2021</TD><TD>2021-Q4</TD><TD>B</TD><TD>F</TD><TD>2011</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>2021/10/01</TD><TD>2021/12/31</TD><TD>4.54</TD><TD>&nbsp;</TD><TD>33</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q1</TD><TD>B</TD><TD>F</TD><TD>2011</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>2022/01/01</TD><TD>2022/03/31</TD><TD>3.98</TD><TD>&nbsp;</TD><TD>32</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q2</TD><TD>B</TD><TD>F</TD><TD>2011</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>2022/04/01</TD><TD>2022/06/30</TD><TD>3.77</TD><TD>&nbsp;</TD><TD>31</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q3</TD><TD>B</TD><TD>F</TD><TD>2011</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>2022/07/01</TD><TD>2022/09/30</TD><TD>3.29</TD><TD>&nbsp;</TD><TD>30</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q4</TD><TD>B</TD><TD>F</TD><TD>2011</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>2022/10/01</TD><TD>2022/12/31</TD><TD>3.34</TD><TD>&nbsp;</TD><TD>29</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q1</TD><TD>C</TD><TD>G</TD><TD>2022</TD><TD>2022/01/01</TD><TD>2022/12/31</TD><TD>2022/01/01</TD><TD>2022/03/31</TD><TD>1195263.63</TD><TD>7961078.94</TD><TD>7</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q1</TD><TD>C</TD><TD>H</TD><TD>2021</TD><TD>2021/01/01</TD><TD>2021/12/31</TD><TD>2022/01/01</TD><TD>2022/03/31</TD><TD>1739761.6</TD><TD>9522905.24</TD><TD>14</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q1</TD><TD>C</TD><TD>I</TD><TD>2020</TD><TD>2020/01/01</TD><TD>2020/12/31</TD><TD>2022/01/01</TD><TD>2022/03/31</TD><TD>&nbsp;</TD><TD>9181739.69</TD><TD>21</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q1</TD><TD>C</TD><TD>J</TD><TD>2014</TD><TD>2014/06/15</TD><TD>2014/12/30</TD><TD>2022/01/01</TD><TD>2022/03/31</TD><TD>&nbsp;</TD><TD>2270329.6</TD><TD>28</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q2</TD><TD>C</TD><TD>G</TD><TD>2022</TD><TD>2022/01/01</TD><TD>2022/12/31</TD><TD>2022/04/01</TD><TD>2022/06/30</TD><TD>-1102847.98</TD><TD>7961078.94</TD><TD>5</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q2</TD><TD>C</TD><TD>H</TD><TD>2021</TD><TD>2021/01/01</TD><TD>2021/12/31</TD><TD>2022/04/01</TD><TD>2022/06/30</TD><TD>-1632043.92</TD><TD>9522905.24</TD><TD>13</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q2</TD><TD>C</TD><TD>I</TD><TD>2020</TD><TD>2020/01/01</TD><TD>2020/12/31</TD><TD>2022/04/01</TD><TD>2022/06/30</TD><TD>247.34</TD><TD>9181739.69</TD><TD>19</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q2</TD><TD>C</TD><TD>J</TD><TD>2014</TD><TD>2014/06/15</TD><TD>2014/12/30</TD><TD>2022/04/01</TD><TD>2022/06/30</TD><TD>117.87</TD><TD>2270329.6</TD><TD>27</TD><TD>False</TD><TD>False</TD></TR><TR><TD>2022</TD><TD>2022-Q3</TD><TD>C</TD><TD>G</TD><TD>2022</TD><TD>2022/01/01</TD><TD>2022/12/31</TD><TD>2022/07/01</TD><TD>2022/09/30</TD><TD>8.52651E-14</TD><TD>7961078.94</TD><TD>3</TD><TD>False</TD><TD>True</TD></TR><TR><TD>2022</TD><TD>2022-Q3</TD><TD>C</TD><TD>H</TD><TD>2021</TD><TD>2021/01/01</TD><TD>2021/12/31</TD><TD>2022/07/01</TD><TD>2022/09/30</TD><TD>7.28306E-14</TD><TD>9522905.24</TD><TD>10</TD><TD>False</TD><TD>True</TD></TR><TR><TD>2022</TD><TD>2022-Q3</TD><TD>C</TD><TD>J</TD><TD>2014</TD><TD>2014/06/15</TD><TD>2014/12/30</TD><TD>2022/07/01</TD><TD>2022/09/30</TD><TD>-1.33227E-14</TD><TD>2270329.6</TD><TD>25</TD><TD>False</TD><TD>True</TD></TR><TR><TD>2022</TD><TD>2022-Q3</TD><TD>C</TD><TD>I</TD><TD>2020</TD><TD>2020/01/01</TD><TD>2020/12/31</TD><TD>2022/07/01</TD><TD>2022/09/30</TD><TD>8.21565E-15</TD><TD>9181739.69</TD><TD>18</TD><TD>False</TD><TD>True</TD></TR><TR><TD>2022</TD><TD>2022-Q4</TD><TD>C</TD><TD>G</TD><TD>2022</TD><TD>2022/01/01</TD><TD>2022/12/31</TD><TD>2022/10/01</TD><TD>2022/12/31</TD><TD>2.38032E-13</TD><TD>7961078.94</TD><TD>1</TD><TD>True</TD><TD>True</TD></TR><TR><TD>2022</TD><TD>2022-Q4</TD><TD>C</TD><TD>H</TD><TD>2021</TD><TD>2021/01/01</TD><TD>2021/12/31</TD><TD>2022/10/01</TD><TD>2022/12/31</TD><TD>-1.47438E-13</TD><TD>9522905.24</TD><TD>9</TD><TD>False</TD><TD>True</TD></TR><TR><TD>2022</TD><TD>2022-Q4</TD><TD>C</TD><TD>J</TD><TD>2014</TD><TD>2014/06/15</TD><TD>2014/12/30</TD><TD>2022/10/01</TD><TD>2022/12/31</TD><TD>7.88258E-15</TD><TD>2270329.6</TD><TD>23</TD><TD>False</TD><TD>True</TD></TR><TR><TD>2022</TD><TD>2022-Q4</TD><TD>C</TD><TD>I</TD><TD>2020</TD><TD>2020/01/01</TD><TD>2020/12/31</TD><TD>2022/10/01</TD><TD>2022/12/31</TD><TD>-1.04361E-14</TD><TD>9181739.69</TD><TD>15</TD><TD>True</TD><TD>True</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>It is the result of merging two sets of data with different sorts of time granularities - a financial dataset with actual revenue by fiscal quarter and quote year, and a quotes dataset which has the effective date and termination date of different deals, as well as the revenue that was projected as at quote time. Basically I'm getting the most recent quote applicable at each fiscal period, and using the quote year to keep older quotes that are still applicable. The row index is calculated in the query based on a sort by Company, Deal, Quote Effective Date and Fiscal Quarter, in order to identify the most recent row that I want to use, and quote last row is a calculated column such that it identifies whether the row is the last for a given quote and a given fiscal year.</P><P>&nbsp;</P><P>I also have a calendar table "Calendar" covering the period, with a join from "Date" to "Quarter Start Date", and a disconnected table with a single column called "Include" which is either True/False that I'm using as a switch (see below).</P><P>&nbsp;</P><P>I'm trying to produce visuals that show the most recent quote revenue to contrast actuals. If I have date as an axis it needs to show the most recent revenue as at that date (in practice I'm not drilling down further than year), but it needs to roll up correctly into subtotals and grand totals (by date) for any other dimensions (company, deal, and many other columns in the real dataset). The measure I'm using in Desktop is as follows, and it works as I want it to:</P><P>&nbsp;</P><DIV><DIV><SPAN>Latest Quote Revenue USD = </SPAN></DIV><PRE><SPAN>VAR</SPAN><SPAN> includeclosed = </SPAN><SPAN>SELECTEDVALUE</SPAN><SPAN>('Closed Business'[Include], TRUE) // This is the disconnected table/slicer switch - not too important</SPAN><BR /><BR /><SPAN>VAR</SPAN><SPAN> filtered = </SPAN><SPAN>FILTER</SPAN><SPAN>('Combined Data', 'Combined Data'[Quarter Start Date] &lt; 'Combined Data'[Quote Termination Date] || ('Combined Data'[Negligible] = FALSE &amp;&amp; includeclosed = TRUE))</SPAN><BR /><BR /><SPAN>VAR</SPAN><SPAN> summ = </SPAN><SPAN>SUMMARIZECOLUMNS</SPAN><SPAN>('Combined Data'[Company], 'Calendar'[Year], filtered, </SPAN><SPAN>"val"</SPAN><SPAN>, </SPAN><BR /><BR /><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN><SPAN>MAX</SPAN><SPAN>('Combined Data'[Quote Estimated Revenue]), </SPAN><SPAN>FILTER</SPAN><SPAN>(filtered, 'Combined Data'[Row Index]=</SPAN><SPAN>MAX</SPAN><SPAN>('Combined Data'[Row Index]))))</SPAN><BR /><BR /><SPAN>&nbsp; &nbsp; </SPAN><SPAN>RETURN</SPAN> <SPAN>SUMX</SPAN><SPAN>(summ, [val])</SPAN></PRE></DIV><P>&nbsp;</P><P>Unfortunately this doesn't work in Fabric for me, and I understand this is because of limitations with SUMMARIZECOLUMNS there. (<SPAN class="">Calculation error in measure 'Combined Data'[Latest Quote Revenue USD]: SummarizeColumns() and AddMissingItems() may not be used in this context.</SPAN>)</P><P>&nbsp;</P><P>What is the correct way to translate this to not use SUMMARIZECOLUMNS? Is there a better approach I could be using overall?</P><P>&nbsp;</P> Mon, 01 Jul 2024 11:35:33 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Replacing-SUMMARIZECOLUMNS-with-SUMMARIZE-to-get-measure-that/m-p/4018430#M158485 MBZA 2024-07-01T11:35:33Z Re: Replacing SUMMARIZECOLUMNS with SUMMARIZE to get measure that works in Desktop to work in Fabric https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Replacing-SUMMARIZECOLUMNS-with-SUMMARIZE-to-get-measure-that/m-p/4018819#M158511 <P>The issue is not with SUMMARIZECOLUMNS. The issue is that you are adding calculations directly, messing with the context.&nbsp; The recommendation is to use ADDCOLUMNS on top of SUMMARIZECOLUMNS.</P> <P>&nbsp;</P> <P>Also, this:&nbsp;<A href="https://xxlbi.com/blog/power-bi-antipatterns-9/" target="_blank">https://xxlbi.com/blog/power-bi-antipatterns-9/</A></P> Mon, 01 Jul 2024 15:09:22 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Replacing-SUMMARIZECOLUMNS-with-SUMMARIZE-to-get-measure-that/m-p/4018819#M158511 lbendlin 2024-07-01T15:09:22Z Re: Replacing SUMMARIZECOLUMNS with SUMMARIZE to get measure that works in Desktop to work in Fabric https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Replacing-SUMMARIZECOLUMNS-with-SUMMARIZE-to-get-measure-that/m-p/4020101#M158590 <P>Thanks for the response, but please can you elaborate? I've seen the pattern of using ADDCOLUMNS with SUMMARIZE (which I can't get to do what I want) but I haven't seen it with SUMMARIZECOLUMNS. In fact all I can see is posts telling people to switch to using SUMMARIZECOLUMNS by itself.</P><P>&nbsp;</P><P>Getting rid of the table filter as below is also changing my results:</P><PRE>Latest Quote Revenue USD test = <BR />VAR includeclosed = SELECTEDVALUE('Closed Business'[Include], TRUE)<BR />VAR summ = SUMMARIZECOLUMNS('Combined Data'[Company], 'Calendar'[Year], 'Combined Data', "val", <BR />CALCULATE(MAX('Combined Data'[Quote Estimated Revenue]), ('Combined Data'[Row Index]=MAX('Combined Data'[Row Index])) &amp;&amp; ('Combined Data'[Quarter Start Date] &lt; 'Combined Data'[Quote Termination Date] || ('Combined Data'[Negligible] = FALSE &amp;&amp; includeclosed = TRUE))))<BR />RETURN SUMX(summ, [val])</PRE><P>&nbsp;</P> Tue, 02 Jul 2024 09:04:39 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Replacing-SUMMARIZECOLUMNS-with-SUMMARIZE-to-get-measure-that/m-p/4020101#M158590 MBZA 2024-07-02T09:04:39Z Re: Replacing SUMMARIZECOLUMNS with SUMMARIZE to get measure that works in Desktop to work in Fabric https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Replacing-SUMMARIZECOLUMNS-with-SUMMARIZE-to-get-measure-that/m-p/4027667#M159111 <P>Please provide sample data that covers your issue or question <STRONG>completely</STRONG>, in a <STRONG>usable</STRONG> format (not as a screenshot).<BR /><BR />Do not include sensitive information or anything not related to the issue or question. <BR /><BR />If you are unsure how to upload data please refer to <A href="https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216" target="_blank">https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216</A> <BR /><BR />Please show the expected outcome based on the sample data you provided. <BR /><BR />Want faster answers? <A href="https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523" target="_blank">https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523</A></P> Fri, 05 Jul 2024 22:59:32 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Replacing-SUMMARIZECOLUMNS-with-SUMMARIZE-to-get-measure-that/m-p/4027667#M159111 lbendlin 2024-07-05T22:59:32Z Re: Replacing SUMMARIZECOLUMNS with SUMMARIZE to get measure that works in Desktop to work in Fabric https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Replacing-SUMMARIZECOLUMNS-with-SUMMARIZE-to-get-measure-that/m-p/4029648#M159274 <P>I managed to resolve this with the following code:</P><P>I added a new column</P><DIV><PRE><SPAN>Quote Last Row excl Negl</SPAN><SPAN>.</SPAN><SPAN> =</SPAN><BR /><SPAN>VAR</SPAN> <SPAN>lastindex</SPAN><SPAN> = </SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN><SPAN>MAXX</SPAN><SPAN>(</SPAN><SPAN>'Combined Data'</SPAN><SPAN>, </SPAN><SPAN>'Combined Data'</SPAN><SPAN>[Row Index]</SPAN><SPAN>-</SPAN><SPAN>IF</SPAN><SPAN>(</SPAN><SPAN>'Combined Data'</SPAN><SPAN>[Negligible]</SPAN><SPAN>,</SPAN><SPAN>100</SPAN><SPAN>,</SPAN><SPAN>0</SPAN><SPAN>)), </SPAN><SPAN>ALLEXCEPT</SPAN><SPAN>(</SPAN><SPAN>'Combined Data'</SPAN><SPAN>, </SPAN><SPAN>'Combined Data'</SPAN><SPAN>[Quote ID]</SPAN><SPAN>, </SPAN><SPAN>'Combined Data'</SPAN><SPAN>[Report Type]</SPAN><SPAN>, </SPAN><SPAN>'Combined Data'</SPAN><SPAN>[FiscalYear]</SPAN><SPAN>))</SPAN><BR /><SPAN>RETURN</SPAN> <SPAN>'Combined Data'</SPAN><SPAN>[Row Index]</SPAN><SPAN> = </SPAN><SPAN>lastindex</SPAN></PRE></DIV><P>then</P><DIV><PRE><SPAN>Latest Quote Revenue USD test2 =</SPAN><BR /><SPAN>VAR</SPAN> <SPAN>includeclosed</SPAN><SPAN> = </SPAN><SPAN>SELECTEDVALUE</SPAN><SPAN>(</SPAN><SPAN>'Closed Business'</SPAN><SPAN>[Include]</SPAN><SPAN>, </SPAN><SPAN>TRUE</SPAN><SPAN>)</SPAN><BR /><SPAN>VAR</SPAN> <SPAN>summ</SPAN><SPAN> = </SPAN><SPAN>ADDCOLUMNS</SPAN><SPAN>(</SPAN><SPAN>SUMMARIZE</SPAN><SPAN>(</SPAN><SPAN>'Combined Data'</SPAN><SPAN>, </SPAN><SPAN>'Combined Data'</SPAN><SPAN>[Company]</SPAN><SPAN>, </SPAN><SPAN>'Calendar'</SPAN><SPAN>[Year]</SPAN><SPAN>, </SPAN><SPAN>'Combined Data'</SPAN><SPAN>[Quote Year]</SPAN><SPAN>),</SPAN><SPAN>"val"</SPAN><SPAN>, </SPAN><BR /><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN><SPAN>MAX</SPAN><SPAN>(</SPAN><SPAN>'Combined Data'</SPAN><SPAN>[Quote Estimated Revenue]</SPAN><SPAN>), </SPAN><SPAN>'Combined Data'</SPAN><SPAN>[Quote Last Row excl Negl.]</SPAN><SPAN> &nbsp;&amp;&amp; (</SPAN><SPAN>'Combined Data'</SPAN><SPAN>[Quarter Start Date]</SPAN><SPAN> &lt; </SPAN><SPAN>'Combined Data'</SPAN><SPAN>[Quote Termination Date]</SPAN><SPAN> || (</SPAN><SPAN>'Combined Data'</SPAN><SPAN>[Negligible]</SPAN><SPAN> = </SPAN><SPAN>FALSE</SPAN><SPAN> &amp;&amp; </SPAN><SPAN>includeclosed</SPAN><SPAN> = </SPAN><SPAN>TRUE</SPAN><SPAN>))))</SPAN><BR /><SPAN>&nbsp; &nbsp; </SPAN><SPAN>RETURN</SPAN> <SPAN>SUMX</SPAN><SPAN>(</SPAN><SPAN>summ</SPAN><SPAN>, </SPAN><SPAN>[val]</SPAN><SPAN>)</SPAN></PRE></DIV> Mon, 08 Jul 2024 08:09:31 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Replacing-SUMMARIZECOLUMNS-with-SUMMARIZE-to-get-measure-that/m-p/4029648#M159274 MBZA 2024-07-08T08:09:31Z