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.

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.