topic TOPN DAX without filter in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/TOPN-DAX-without-filter/m-p/3775960#M147566 <P>I have an issue with the TOPN method.&nbsp;<BR /><BR />I have a table with a few columns with different Water parameters, they were filled in on different days&nbsp; (so they have gaps) "InsertDate" column shows these dates. it is also based on different tanks.</P><TABLE><TBODY><TR><TD>Tank</TD><TD>InsertDate</TD><TD>HardnessF</TD><TD>Alkalinity</TD><TD>ph</TD><TD>Conductivity</TD><TD>Temperature</TD></TR><TR><TD>111</TD><TD>13-03-24 11:14</TD><TD>25.54</TD><TD>&nbsp;</TD><TD>6.88</TD><TD>808</TD><TD>15</TD></TR><TR><TD>111</TD><TD>12-03-24 9:27</TD><TD>26.07</TD><TD>&nbsp;</TD><TD>6.94</TD><TD>807.2</TD><TD>15.3</TD></TR><TR><TD>111</TD><TD>11-03-24 10:42</TD><TD>24.29</TD><TD>2.96</TD><TD>6.97</TD><TD>803.5</TD><TD>15.6</TD></TR><TR><TD>112</TD><TD>11-03-24 10:40</TD><TD>24.29</TD><TD>2.96</TD><TD>6.97</TD><TD>803.5</TD><TD>15.6</TD></TR><TR><TD>112</TD><TD>11-03-24 10:34</TD><TD>22.9</TD><TD>1.73</TD><TD>7.05</TD><TD>511.9</TD><TD>11.5</TD></TR><TR><TD>112</TD><TD>11-03-24 10:30</TD><TD>26.3</TD><TD>2.97</TD><TD>6.94</TD><TD>831.9</TD><TD>15.4</TD></TR><TR><TD>112</TD><TD>11-03-24 7:15</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>7.772</TD><TD>11.9</TD></TR></TBODY></TABLE><P><BR />I made a Line charts which show me a trends of value for every parameter based on Insert date. and two slicers for user: "InserDate", and "Tank" to select each tank.<BR /><BR /><STRONG>ISUEE: I would like to measure the +/- 3sigma value for each parameter based on only last 10 samples, independently/without the "InserDate" slicer but I can't handle how to remove a filter from this "InserDate" column only for TOPN method.</STRONG></P><DIV><DIV><SPAN><BR />Here is my code.<BR />+</SPAN><SPAN>3</SPAN><SPAN>sigma_HardnessF_last10samples =</SPAN></DIV><DIV><SPAN>VAR</SPAN> <SPAN>lastxsamples</SPAN><SPAN> =</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>TOPN</SPAN><SPAN> (</SPAN><SPAN>10</SPAN><SPAN>, </SPAN><STRONG>WaterData</STRONG><SPAN>,</SPAN><SPAN>'WaterData'</SPAN><SPAN>[HardnessF]</SPAN><SPAN>,</SPAN><SPAN>DESC</SPAN><SPAN>) </SPAN></DIV><DIV><SPAN>RETURN</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>IF</SPAN><SPAN>(</SPAN><SPAN>SELECTEDVALUE</SPAN><SPAN>(</SPAN><SPAN>WaterData</SPAN><SPAN>[Location]</SPAN><SPAN>)=</SPAN><SPAN>"Poland"</SPAN><SPAN>, </SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>AVERAGE</SPAN><SPAN>(</SPAN><SPAN>'WaterData'</SPAN><SPAN>[HardnessF]</SPAN><SPAN>)+(</SPAN><SPAN>3</SPAN><SPAN>*</SPAN><SPAN>STDEV.P</SPAN><SPAN>(</SPAN><SPAN>'WaterData'</SPAN><SPAN>[HardnessF]</SPAN><SPAN>)),</SPAN><SPAN>lastxsamples</SPAN><SPAN>),</SPAN><SPAN>""</SPAN><SPAN>)<BR /><BR />I will be grateful for any help.<BR /></SPAN></DIV></DIV> Tue, 19 Mar 2024 16:30:56 GMT PijanMac 2024-03-19T16:30:56Z TOPN DAX without filter https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/TOPN-DAX-without-filter/m-p/3775960#M147566 <P>I have an issue with the TOPN method.&nbsp;<BR /><BR />I have a table with a few columns with different Water parameters, they were filled in on different days&nbsp; (so they have gaps) "InsertDate" column shows these dates. it is also based on different tanks.</P><TABLE><TBODY><TR><TD>Tank</TD><TD>InsertDate</TD><TD>HardnessF</TD><TD>Alkalinity</TD><TD>ph</TD><TD>Conductivity</TD><TD>Temperature</TD></TR><TR><TD>111</TD><TD>13-03-24 11:14</TD><TD>25.54</TD><TD>&nbsp;</TD><TD>6.88</TD><TD>808</TD><TD>15</TD></TR><TR><TD>111</TD><TD>12-03-24 9:27</TD><TD>26.07</TD><TD>&nbsp;</TD><TD>6.94</TD><TD>807.2</TD><TD>15.3</TD></TR><TR><TD>111</TD><TD>11-03-24 10:42</TD><TD>24.29</TD><TD>2.96</TD><TD>6.97</TD><TD>803.5</TD><TD>15.6</TD></TR><TR><TD>112</TD><TD>11-03-24 10:40</TD><TD>24.29</TD><TD>2.96</TD><TD>6.97</TD><TD>803.5</TD><TD>15.6</TD></TR><TR><TD>112</TD><TD>11-03-24 10:34</TD><TD>22.9</TD><TD>1.73</TD><TD>7.05</TD><TD>511.9</TD><TD>11.5</TD></TR><TR><TD>112</TD><TD>11-03-24 10:30</TD><TD>26.3</TD><TD>2.97</TD><TD>6.94</TD><TD>831.9</TD><TD>15.4</TD></TR><TR><TD>112</TD><TD>11-03-24 7:15</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>7.772</TD><TD>11.9</TD></TR></TBODY></TABLE><P><BR />I made a Line charts which show me a trends of value for every parameter based on Insert date. and two slicers for user: "InserDate", and "Tank" to select each tank.<BR /><BR /><STRONG>ISUEE: I would like to measure the +/- 3sigma value for each parameter based on only last 10 samples, independently/without the "InserDate" slicer but I can't handle how to remove a filter from this "InserDate" column only for TOPN method.</STRONG></P><DIV><DIV><SPAN><BR />Here is my code.<BR />+</SPAN><SPAN>3</SPAN><SPAN>sigma_HardnessF_last10samples =</SPAN></DIV><DIV><SPAN>VAR</SPAN> <SPAN>lastxsamples</SPAN><SPAN> =</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>TOPN</SPAN><SPAN> (</SPAN><SPAN>10</SPAN><SPAN>, </SPAN><STRONG>WaterData</STRONG><SPAN>,</SPAN><SPAN>'WaterData'</SPAN><SPAN>[HardnessF]</SPAN><SPAN>,</SPAN><SPAN>DESC</SPAN><SPAN>) </SPAN></DIV><DIV><SPAN>RETURN</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>IF</SPAN><SPAN>(</SPAN><SPAN>SELECTEDVALUE</SPAN><SPAN>(</SPAN><SPAN>WaterData</SPAN><SPAN>[Location]</SPAN><SPAN>)=</SPAN><SPAN>"Poland"</SPAN><SPAN>, </SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>AVERAGE</SPAN><SPAN>(</SPAN><SPAN>'WaterData'</SPAN><SPAN>[HardnessF]</SPAN><SPAN>)+(</SPAN><SPAN>3</SPAN><SPAN>*</SPAN><SPAN>STDEV.P</SPAN><SPAN>(</SPAN><SPAN>'WaterData'</SPAN><SPAN>[HardnessF]</SPAN><SPAN>)),</SPAN><SPAN>lastxsamples</SPAN><SPAN>),</SPAN><SPAN>""</SPAN><SPAN>)<BR /><BR />I will be grateful for any help.<BR /></SPAN></DIV></DIV> Tue, 19 Mar 2024 16:30:56 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/TOPN-DAX-without-filter/m-p/3775960#M147566 PijanMac 2024-03-19T16:30:56Z Re: TOPN DAX without filter https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/TOPN-DAX-without-filter/m-p/3776952#M147616 <P>Hi&nbsp; <a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="461649" data-lia-user-login="PijanMac" class="lia-mention lia-mention-user">PijanMac</a>&nbsp;,</P> <P>&nbsp;</P> <P>You can try the following DAX:</P> <LI-CODE lang="markup">Rank = RANKX( FILTER(ALL('WaterData'),'WaterData'[Tank]=MAX('WaterData'[Tank])),CALCULATE(SUM('WaterData'[HardnessF])),,DESC)</LI-CODE><LI-CODE lang="markup">+3sigma_HardnessF_last10samples = IF( SELECTEDVALUE('WaterData'[Location])="Poland", CALCULATE( AVERAGE('WaterData'[HardnessF])+ 3*STDEVX.P( FILTER(ALL('WaterData'), [Rank]&lt;=10),[HardnessF])))</LI-CODE> <P>&nbsp;</P> <P>&nbsp;</P> <P>Best Regards,</P> <P>Liu Yang</P> <P>If this post <STRONG>helps</STRONG>, then please consider <EM>Accept it as the solution</EM> to help the other members find it more quickly.</P> <P>&nbsp;</P> Wed, 20 Mar 2024 03:40:19 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/TOPN-DAX-without-filter/m-p/3776952#M147616 Anonymous 2024-03-20T03:40:19Z