Forum Discussion
Distinct Count by filter
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 |
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 ResultThe 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.
8 Replies
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.
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]) ) )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.
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?Yes, you are right
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 ResultThe 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.
