topic Re: Count of tickets which are overdue and have specific values in certain columns in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-of-tickets-which-are-overdue-and-have-specific-values-in/m-p/1939725#M42201 <P><img /></P><P>Here's a screenshot of my data table in case my original post wasn't clear.</P> Tue, 06 Jul 2021 09:50:54 GMT Anonymous 2021-07-06T09:50:54Z Count of tickets which are overdue and have specific values in certain columns https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-of-tickets-which-are-overdue-and-have-specific-values-in/m-p/1939595#M42198 <P>Hello everyone,</P><P>&nbsp;</P><P>I am trying to get a card visual to show the count of tickets that have <STRONG>Overdue Days</STRONG> &gt; 5 and are in a <STRONG>State</STRONG> of <EM>Open</EM>&nbsp;or <EM>Work In Progress and</EM>&nbsp;an <STRONG>Approval state </STRONG>of <EM>Approved</EM>, <EM>Pending CAB</EM>, or <EM>Retrospective</EM>.</P><P>&nbsp;</P><P>Here is my example Table:</P><TABLE><TBODY><TR><TD><STRONG>Number</STRONG></TD><TD><STRONG>State</STRONG></TD><TD><STRONG>Approval state</STRONG></TD><TD><STRONG>Planned End Date</STRONG></TD><TD><STRONG>Overdue Days</STRONG></TD><TD><FONT color="#FF0000"><STRONG>Would be counted</STRONG></FONT></TD></TR><TR><TD>CHG0049874</TD><TD>Closed Complete</TD><TD>Approved</TD><TD>07/05/2021 16:55</TD><TD>60</TD><TD>&nbsp;</TD></TR><TR><TD>CHG0050207</TD><TD>Open</TD><TD>Approved</TD><TD>06/07/2021 20:00</TD><TD>0</TD><TD>&nbsp;</TD></TR><TR><TD>CHG0050176</TD><TD>Work in Progress</TD><TD>Retrospective</TD><TD>09/05/2021 11:00</TD><TD>62</TD><TD><STRONG><FONT color="#FF0000">1</FONT></STRONG></TD></TR><TR><TD>CHG0050156</TD><TD>Open</TD><TD>Pending CAB</TD><TD>03/08/2021 10:00</TD><TD>-28</TD><TD>&nbsp;</TD></TR><TR><TD>CHG0050095</TD><TD>Open</TD><TD>Pending CAB</TD><TD>25/06/2021 14:00</TD><TD>11</TD><TD><STRONG><FONT color="#FF0000">1</FONT></STRONG></TD></TR><TR><TD>CHG0049959</TD><TD>Closed Failed</TD><TD>Approved</TD><TD>09/06/2021 17:00</TD><TD>27</TD><TD>&nbsp;</TD></TR><TR><TD>CHG0049069</TD><TD>Closed Incomplete</TD><TD>Approved</TD><TD>14/01/2021 12:00</TD><TD>173</TD><TD>&nbsp;</TD></TR><TR><TD>CHG0048728</TD><TD>Closed Failed</TD><TD>Approved</TD><TD>27/10/2020 11:45</TD><TD>252</TD><TD>&nbsp;</TD></TR><TR><TD>CHG0050054</TD><TD>Work in Progress</TD><TD>Retrospective</TD><TD>30/06/2021 10:42</TD><TD>6</TD><TD><STRONG><FONT color="#FF0000">1</FONT></STRONG></TD></TR><TR><TD>CHG0050045</TD><TD>Work in Progress</TD><TD>Approved</TD><TD>17/06/2021 12:16</TD><TD>19</TD><TD><STRONG><FONT color="#FF0000">1</FONT></STRONG></TD></TR><TR><TD>CHG0049491</TD><TD>Rescheduling</TD><TD>Pending CAB</TD><TD>26/03/2021 11:00</TD><TD>102</TD><TD><STRONG><FONT color="#FF0000">1</FONT></STRONG></TD></TR><TR><TD>CHG0049588</TD><TD>Rescheduling</TD><TD>Approved</TD><TD>30/04/2021 11:30</TD><TD>67</TD><TD><STRONG><FONT color="#FF0000">1</FONT></STRONG></TD></TR><TR><TD>CHG0048692</TD><TD>Rescheduling</TD><TD>Approved</TD><TD>17/10/2020 04:00</TD><TD>262</TD><TD><STRONG><FONT color="#FF0000">1</FONT></STRONG></TD></TR><TR><TD>CHG0050091</TD><TD>Work in Progress</TD><TD>Retrospective</TD><TD>16/07/2021 15:47</TD><TD>-10</TD><TD>&nbsp;</TD></TR><TR><TD>CHG0045214</TD><TD>Closed Rolled Back</TD><TD>Approved</TD><TD>22/02/2019 22:30</TD><TD>865</TD><TD>&nbsp;</TD></TR><TR><TD>CHG0046766</TD><TD>Closed Rolled Back</TD><TD>Approved</TD><TD>21/12/2019 12:30</TD><TD>563</TD><TD>&nbsp;</TD></TR><TR><TD>CHG0050143</TD><TD>Work in Progress</TD><TD>Approved</TD><TD>09/07/2021 20:33</TD><TD>-3</TD><TD>&nbsp;</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>In the above example, I would need it to return the value of <STRONG><FONT color="#339966">7</FONT></STRONG></P><P>&nbsp;</P><P><FONT color="#000000">Any help would be most appreciated!</FONT></P> Tue, 06 Jul 2021 09:43:45 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-of-tickets-which-are-overdue-and-have-specific-values-in/m-p/1939595#M42198 Anonymous 2021-07-06T09:43:45Z Re: Count of tickets which are overdue and have specific values in certain columns https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-of-tickets-which-are-overdue-and-have-specific-values-in/m-p/1939725#M42201 <P><img /></P><P>Here's a screenshot of my data table in case my original post wasn't clear.</P> Tue, 06 Jul 2021 09:50:54 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-of-tickets-which-are-overdue-and-have-specific-values-in/m-p/1939725#M42201 Anonymous 2021-07-06T09:50:54Z Re: Count of tickets which are overdue and have specific values in certain columns https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-of-tickets-which-are-overdue-and-have-specific-values-in/m-p/1940167#M42214 <LI-CODE lang="csharp">[Count] = CALCULATE( // Assumpition: Tickets // is a table that has one row // per a unique ticket. COUNTROWS( Tickets ), KEEPFILTERS( Tickets[Overdue Days] &gt; 5 ), KEEPFILTERS( Tickets[State] in { "Open", "Work in Progress" } ), KEEPFILTERS( Tickets[Approval State] in { "Approved", "Pending CAB", "Retrospective" } ) )</LI-CODE> Tue, 06 Jul 2021 13:07:22 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-of-tickets-which-are-overdue-and-have-specific-values-in/m-p/1940167#M42214 Anonymous 2021-07-06T13:07:22Z Re: Count of tickets which are overdue and have specific values in certain columns https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-of-tickets-which-are-overdue-and-have-specific-values-in/m-p/1940187#M42215 <P>Perfect!&nbsp; Exactly what I needed.&nbsp; Many Thanks</P> Tue, 06 Jul 2021 13:20:54 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-of-tickets-which-are-overdue-and-have-specific-values-in/m-p/1940187#M42215 Anonymous 2021-07-06T13:20:54Z