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.

tamerj1's avatar
tamerj1
Community Champion
4 years ago

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

    Yes, you are right

    • tamerj1's avatar
      tamerj1
      Community 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.