topic Re: Countrows with removefilters not working as it should in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Countrows-with-removefilters-not-working-as-it-should/m-p/3688096#M143280 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="680244" data-lia-user-login="Meeshe" class="lia-mention lia-mention-user">Meeshe</a>&nbsp;</P> <P>&nbsp;</P> <P>I added the date table as a slicer.</P> <P><img /></P> <P>&nbsp;</P> <P>You can try the following measure:</P> <LI-CODE lang="markup">countRows = CALCULATE(COUNT('Table'[ID]), FILTER(ALL('Table'), [OfferDate] &gt;= MIN('Date'[Date]) &amp;&amp; [OfferDate] &lt;= MAX('Date'[Date])))</LI-CODE> <P>&nbsp;</P> <P><img /></P> <P>Is this the result you expect?</P> <P>&nbsp;</P> <P>Best Regards,<BR />Community Support Team _Yuliax</P> <P>&nbsp;</P> <P>If this post <EM><STRONG>helps</STRONG></EM>, then please consider <EM><STRONG>Accept it as the solution</STRONG></EM> to help the other members find it more quickly.</P> Thu, 08 Feb 2024 11:00:14 GMT Anonymous 2024-02-08T11:00:14Z Countrows with removefilters not working as it should https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Countrows-with-removefilters-not-working-as-it-should/m-p/3686219#M143186 <P>I have the following DAX measure to display a row count figure on a card. There are <SPAN>two slicers, one for the Offer Date (using an all dates table) and one for the Status. When selecting a date range in the Offer Date filter, e.g. 01/02/2024-06/02/2024, it should output a figure of 26.&nbsp;</SPAN>When the Status filter is selected as well as the Offer Date filter, e.g. Accepted, it changes the figure to something completely unrelatable (19!), meaning the DAX measure DOES NOT IGNORE other filters as expected.</P><P>&nbsp;</P><DIV><DIV><SPAN>CALCULATE(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; COUNTROWS(TestData),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; REMOVEFILTERS(TestData),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; VALUES(TestData[OfferDate])</SPAN></DIV><DIV><SPAN>)</SPAN></DIV><DIV>&nbsp;</DIV><DIV>&nbsp;</DIV><DIV><TABLE><TBODY><TR><TD>ID</TD><TD>Status</TD><TD>OfferDate</TD></TR><TR><TD>29533</TD><TD>Under Offer</TD><TD>06/02/2024</TD></TR><TR><TD>29532</TD><TD>Under Offer</TD><TD>06/02/2024</TD></TR><TR><TD>29531</TD><TD>Under Offer</TD><TD>06/02/2024</TD></TR><TR><TD>29530</TD><TD>Under Offer</TD><TD>06/02/2024</TD></TR><TR><TD>29529</TD><TD>Under Offer</TD><TD>06/02/2024</TD></TR><TR><TD>29528</TD><TD>Under Offer</TD><TD>06/02/2024</TD></TR><TR><TD>29527</TD><TD>Under Offer</TD><TD>06/02/2024</TD></TR><TR><TD>29525</TD><TD>Under Offer</TD><TD>05/02/2024</TD></TR><TR><TD>29523</TD><TD>Under Offer</TD><TD>05/02/2024</TD></TR><TR><TD>29522</TD><TD>Under Offer</TD><TD>05/02/2024</TD></TR><TR><TD>29505</TD><TD>Under Offer</TD><TD>05/02/2024</TD></TR><TR><TD>29504</TD><TD>Under Offer</TD><TD>05/02/2024</TD></TR><TR><TD>29502</TD><TD>Accepted</TD><TD>05/02/2024</TD></TR><TR><TD>29501</TD><TD>Under Offer</TD><TD>05/02/2024</TD></TR><TR><TD>29499</TD><TD>Under Offer</TD><TD>05/02/2024</TD></TR><TR><TD>29498</TD><TD>Under Offer</TD><TD>02/02/2024</TD></TR><TR><TD>29497</TD><TD>Under Offer</TD><TD>02/02/2024</TD></TR><TR><TD>29496</TD><TD>Under Offer</TD><TD>02/02/2024</TD></TR><TR><TD>29493</TD><TD>Declined</TD><TD>02/02/2024</TD></TR><TR><TD>29492</TD><TD>Accepted</TD><TD>02/02/2024</TD></TR><TR><TD>29491</TD><TD>Under Offer</TD><TD>01/02/2024</TD></TR><TR><TD>29490</TD><TD>Under Offer</TD><TD>01/02/2024</TD></TR><TR><TD>29489</TD><TD>Let</TD><TD>01/02/2024</TD></TR><TR><TD>29488</TD><TD>Declined</TD><TD>01/02/2024</TD></TR><TR><TD>29486</TD><TD>Under Offer</TD><TD>01/02/2024</TD></TR><TR><TD>29485</TD><TD>Accepted</TD><TD>01/02/2024</TD></TR><TR><TD>29483</TD><TD>Under Offer</TD><TD>31/01/2024</TD></TR><TR><TD>29482</TD><TD>Under Offer</TD><TD>31/01/2024</TD></TR><TR><TD>29481</TD><TD>Under Offer</TD><TD>31/01/2024</TD></TR><TR><TD>29480</TD><TD>Under Offer</TD><TD>31/01/2024</TD></TR><TR><TD>29479</TD><TD>Accepted</TD><TD>31/01/2024</TD></TR><TR><TD>29478</TD><TD>Under Offer</TD><TD>31/01/2024</TD></TR><TR><TD>29477</TD><TD>Under Offer</TD><TD>31/01/2024</TD></TR><TR><TD>29476</TD><TD>Under Offer</TD><TD>31/01/2024</TD></TR><TR><TD>29475</TD><TD>Declined</TD><TD>31/01/2024</TD></TR><TR><TD>29474</TD><TD>Under Offer</TD><TD>31/01/2024</TD></TR><TR><TD>29473</TD><TD>Under Offer</TD><TD>31/01/2024</TD></TR><TR><TD>29472</TD><TD>Let</TD><TD>30/01/2024</TD></TR><TR><TD>29471</TD><TD>Under Offer</TD><TD>30/01/2024</TD></TR><TR><TD>29470</TD><TD>Under Offer</TD><TD>30/01/2024</TD></TR><TR><TD>29469</TD><TD>Under Offer</TD><TD>30/01/2024</TD></TR><TR><TD>29468</TD><TD>Accepted</TD><TD>30/01/2024</TD></TR><TR><TD>29467</TD><TD>Under Offer</TD><TD>30/01/2024</TD></TR><TR><TD>29466</TD><TD>Under Offer</TD><TD>30/01/2024</TD></TR><TR><TD>29465</TD><TD>Under Offer</TD><TD>30/01/2024</TD></TR><TR><TD>29464</TD><TD>Under Offer</TD><TD>30/01/2024</TD></TR><TR><TD>29463</TD><TD>Under Offer</TD><TD>30/01/2024</TD></TR><TR><TD>29462</TD><TD>Accepted</TD><TD>30/01/2024</TD></TR><TR><TD>29461</TD><TD>Accepted</TD><TD>30/01/2024</TD></TR><TR><TD>29460</TD><TD>Declined</TD><TD>30/01/2024</TD></TR><TR><TD>29459</TD><TD>Let</TD><TD>29/01/2024</TD></TR><TR><TD>29458</TD><TD>Under Offer</TD><TD>29/01/2024</TD></TR><TR><TD>29457</TD><TD>Under Offer</TD><TD>29/01/2024</TD></TR><TR><TD>29456</TD><TD>Under Offer</TD><TD>29/01/2024</TD></TR><TR><TD>29455</TD><TD>Declined</TD><TD>29/01/2024</TD></TR><TR><TD>29454</TD><TD>Under Offer</TD><TD>29/01/2024</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Thank you for any insight <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P></DIV></DIV> Thu, 08 Feb 2024 10:07:07 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Countrows-with-removefilters-not-working-as-it-should/m-p/3686219#M143186 Meeshe 2024-02-08T10:07:07Z Re: Countrows with removefilters not working as it should https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Countrows-with-removefilters-not-working-as-it-should/m-p/3686912#M143221 <P>Your DAX is a bit redundant.&nbsp; Please explain what you are actualy trying to compute.&nbsp; What is the expected outcome based on the sample data?</P> Wed, 07 Feb 2024 23:42:31 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Countrows-with-removefilters-not-working-as-it-should/m-p/3686912#M143221 lbendlin 2024-02-07T23:42:31Z Re: Countrows with removefilters not working as it should https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Countrows-with-removefilters-not-working-as-it-should/m-p/3687990#M143275 <P>Apologies, I'd written it in a hurry and updated it now - thank you for your response.</P> Thu, 08 Feb 2024 10:07:47 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Countrows-with-removefilters-not-working-as-it-should/m-p/3687990#M143275 Meeshe 2024-02-08T10:07:47Z Re: Countrows with removefilters not working as it should https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Countrows-with-removefilters-not-working-as-it-should/m-p/3688096#M143280 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="680244" data-lia-user-login="Meeshe" class="lia-mention lia-mention-user">Meeshe</a>&nbsp;</P> <P>&nbsp;</P> <P>I added the date table as a slicer.</P> <P><img /></P> <P>&nbsp;</P> <P>You can try the following measure:</P> <LI-CODE lang="markup">countRows = CALCULATE(COUNT('Table'[ID]), FILTER(ALL('Table'), [OfferDate] &gt;= MIN('Date'[Date]) &amp;&amp; [OfferDate] &lt;= MAX('Date'[Date])))</LI-CODE> <P>&nbsp;</P> <P><img /></P> <P>Is this the result you expect?</P> <P>&nbsp;</P> <P>Best Regards,<BR />Community Support Team _Yuliax</P> <P>&nbsp;</P> <P>If this post <EM><STRONG>helps</STRONG></EM>, then please consider <EM><STRONG>Accept it as the solution</STRONG></EM> to help the other members find it more quickly.</P> Thu, 08 Feb 2024 11:00:14 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Countrows-with-removefilters-not-working-as-it-should/m-p/3688096#M143280 Anonymous 2024-02-08T11:00:14Z Re: Countrows with removefilters not working as it should https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Countrows-with-removefilters-not-working-as-it-should/m-p/3708648#M144282 <P>Thank you so much. The dates would come from user input slicers. I did find some DAX that worked in the end:</P><P>&nbsp;</P><DIV><DIV><SPAN>&nbsp; </SPAN><SPAN>CALCULATE</SPAN><SPAN>( </SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN><SPAN>CountRows</SPAN><SPAN>(</SPAN><SPAN>AllocationOffersData</SPAN><SPAN>),</SPAN><SPAN>AllocationOffersData</SPAN><SPAN>,</SPAN><SPAN>Values</SPAN><SPAN>(</SPAN><SPAN>AllocationOffersData</SPAN><SPAN>[Offer Date]</SPAN><SPAN>)), </SPAN><SPAN>-- Calculates number of rows with the filter for Offer Date</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>REMOVEFILTERS</SPAN><SPAN>((</SPAN><SPAN>AllocationOffersData</SPAN><SPAN>[Offer Status]</SPAN><SPAN>) </SPAN><SPAN>-- Clears the filter for Status</SPAN></DIV><DIV><SPAN>)</SPAN></DIV></DIV> Mon, 19 Feb 2024 16:58:06 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Countrows-with-removefilters-not-working-as-it-should/m-p/3708648#M144282 Meeshe 2024-02-19T16:58:06Z Re: Countrows with removefilters not working as it should https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Countrows-with-removefilters-not-working-as-it-should/m-p/3708651#M144283 <P>Thank you for all your replies. I found the following DAX works, I don't know how....but it works!</P><P>&nbsp;</P><DIV><DIV><SPAN>&nbsp; </SPAN><SPAN>CALCULATE</SPAN><SPAN>( </SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN><SPAN>CountRows</SPAN><SPAN>(</SPAN><SPAN>AllocationOffersData</SPAN><SPAN>),</SPAN><SPAN>AllocationOffersData</SPAN><SPAN>,</SPAN><SPAN>Values</SPAN><SPAN>(</SPAN><SPAN>AllocationOffersData</SPAN><SPAN>[Offer Date]</SPAN><SPAN>)), </SPAN><SPAN>-- Calculates number of rows with the filter for Offer Date</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>REMOVEFILTERS</SPAN><SPAN>((</SPAN><SPAN>AllocationOffersData</SPAN><SPAN>[Offer Status]</SPAN><SPAN>) </SPAN><SPAN>-- Clears the filter for Status</SPAN></DIV><DIV><SPAN>)</SPAN></DIV></DIV> Mon, 19 Feb 2024 16:58:52 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Countrows-with-removefilters-not-working-as-it-should/m-p/3708651#M144283 Meeshe 2024-02-19T16:58:52Z