Count of tickets which are overdue and have specific values in certain columns

[Anonymous]

Hello everyone,

I am trying to get a card visual to show the count of tickets that have Overdue Days > 5 and are in a State of Open or Work In Progress and an Approval state of Approved, Pending CAB, or Retrospective.

Here is my example Table:

Number	State	Approval state	Planned End Date	Overdue Days	Would be counted
CHG0049874	Closed Complete	Approved	07/05/2021 16:55	60
CHG0050207	Open	Approved	06/07/2021 20:00	0
CHG0050176	Work in Progress	Retrospective	09/05/2021 11:00	62	1
CHG0050156	Open	Pending CAB	03/08/2021 10:00	-28
CHG0050095	Open	Pending CAB	25/06/2021 14:00	11	1
CHG0049959	Closed Failed	Approved	09/06/2021 17:00	27
CHG0049069	Closed Incomplete	Approved	14/01/2021 12:00	173
CHG0048728	Closed Failed	Approved	27/10/2020 11:45	252
CHG0050054	Work in Progress	Retrospective	30/06/2021 10:42	6	1
CHG0050045	Work in Progress	Approved	17/06/2021 12:16	19	1
CHG0049491	Rescheduling	Pending CAB	26/03/2021 11:00	102	1
CHG0049588	Rescheduling	Approved	30/04/2021 11:30	67	1
CHG0048692	Rescheduling	Approved	17/10/2020 04:00	262	1
CHG0050091	Work in Progress	Retrospective	16/07/2021 15:47	-10
CHG0045214	Closed Rolled Back	Approved	22/02/2019 22:30	865
CHG0046766	Closed Rolled Back	Approved	21/12/2019 12:30	563
CHG0050143	Work in Progress	Approved	09/07/2021 20:33	-3

In the above example, I would need it to return the value of 7

Any help would be most appreciated!

[Anonymous · Accepted solution]

[Count] =
CALCULATE(
    // Assumpition: Tickets
    // is a table that has one row
    // per a unique ticket.
    COUNTROWS( Tickets ),
    KEEPFILTERS(
        Tickets[Overdue Days] > 5
    ),
    KEEPFILTERS(
        Tickets[State] in {
            "Open",
            "Work in Progress"
        }
    ),
    KEEPFILTERS(
        Tickets[Approval State] in {
            "Approved",
            "Pending CAB",
            "Retrospective"
        }
    )
)

[Anonymous]

Perfect!  Exactly what I needed.  Many Thanks

[Anonymous]

Here's a screenshot of my data table in case my original post wasn't clear.