Forum Discussion

vaalyushin's avatar
vaalyushin
Helper I
4 years ago
Solved

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: ...
@Dax
@help
  • tamerj1's avatar
    tamerj1
    4 years ago

    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's avatar
Russell-PBI
Resolver II
4 years ago

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_numbercurrent_statuscreated_datestatus_date_changelatest_status_flag
1open1.01.20221.01.20220
1in progress2.01.20222.01.20221
2open2.02.20222.02.20220
2in progress2.02.20222.02.20220
2closed2.02.20222.02.20221
3open3.02.20233.02.20231

 

CALCULATE(COUNTROWS(YourDataset), latest_status_flag = 1)

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

 

EDIT: COUNTROWS.

vaalyushin's avatar
vaalyushin
Helper I
4 years ago

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's avatar
    Russell-PBI
    Resolver II
    4 years ago

    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's avatar
      vaalyushin
      Helper I
      4 years ago

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

      • Russell-PBI's avatar
        Russell-PBI
        Resolver II
        4 years ago

        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.