Distinct Count by filter

[vaalyushin]

Good afternoon!

I need to get the number of open and closed applications. At the same time, the application may have a history of its states (open, in operation, closed, resolved)

My dataset:

ticket_number	current_status	created_date	status_date_change
1	open	1.01.2022	1.01.2022
1	in progress	2.01.2022	2.01.2022
2	open	2.02.2022	2.02.2022
2	in progress	2.02.2022	2.02.2022
2	closed	2.02.2022	2.02.2022
3	open	3.02.2023	3.02.2023

[tamerj1]

Hi @vaalyushin 
You have only options for the satus which are: Open, then it will become In Progress (In Operation) then it will be either Closed or Resolved. Am I right? Do you have other options?

[vaalyushin]

Yes, you are right

[tamerj1 · Accepted solution]

Hi @vaalyushin 
Here is the sample file with the solution https://www.dropbox.com/t/fZytnjZorSijq226
A new calculated column has to be created:

Status = 
VAR CurrentTicket = Tickets[ticket_number]
VAR CurrentTicketAllAvailableStatuses =
    FILTER (
        Tickets,
        Tickets[ticket_number] = CurrentTicket
    )
VAR Result =
    SWITCH (
        TRUE,
        CONTAINS ( CurrentTicketAllAvailableStatuses, Tickets[current_status], "Resolved" ), "Resolved",
        CONTAINS ( CurrentTicketAllAvailableStatuses, Tickets[current_status], "Closed" ), "Closed",
        CONTAINS ( CurrentTicketAllAvailableStatuses, Tickets[current_status], "Resolved" ), "Resolved",
        CONTAINS ( CurrentTicketAllAvailableStatuses, Tickets[current_status], "In Progress" ), "In Progress",
        "Open"
    )
RETURN
    Result 

The measure is a simple distinct count 

Ticket Count = 
    COUNTROWS ( DISTINCT ( Tickets[ticket_number] ) )

 You can use the newly caculated column in rows or slicers. And the measure will give you the distinct count as per your selection.

[Russell-PBI]

Could you create a flag in your dataset to identify the latest status for each ticket based on created_date and a sort of the current_status, then filter for this in your report or within a measure?

ticket_number	current_status	created_date	status_date_change	latest_status_flag
1	open	1.01.2022	1.01.2022	0
1	in progress	2.01.2022	2.01.2022	1
2	open	2.02.2022	2.02.2022	0
2	in progress	2.02.2022	2.02.2022	0
2	closed	2.02.2022	2.02.2022	1
3	open	3.02.2023	3.02.2023	1

CALCULATE(COUNTROWS(YourDataset), latest_status_flag = 1)

Might be easier than trying to serve it all up in a measure.

EDIT: COUNTROWS.

[vaalyushin]

I didn't fully understand what you meant. now I do it through the measure, but naturally I get the wrong result

CountOpenTickets = 
CALCULATE (
    DISTINCTCOUNT('tickets'[issue_number] ),
    FILTER (
        'tickets',
        'tickets'[current_status] = "Open"
            && 'tickets'[creation_date] = MAX ('jira_vulnerability_issues'[creation_date])
    )
)

[Russell-PBI]

This probably isn't the most streamlined solution as I don't use PowerQuery for transformations, but here is what I came up with: https://drive.google.com/file/d/16XYzwjHyqbhbhbSodc03yXOPorGpj_V3/view?usp=sharing

Effectively, it works out the latest created_date per ticket, the latest current_status based on what will most likely come after each status, then uses the combination of those two bits of information to decide which record is the latest per ticket. If that doesn't work because status will change back and forth, then you might need to consider adding date and time into your dataset rather than just date.

[vaalyushin]

Thank you, It's nice, but i think it can be resolve with measure

[Russell-PBI]

@tamerj1's posted solution works with a calculated column and measure, so no messing about with PowerQuery. I'd say that's a good solution for you.