Forum Discussion

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

Yes, you are right

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
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.