Forum Discussion

SebastianCostas's avatar
SebastianCostas
Frequent Visitor
4 years ago
Solved

Distinct Count only when Count over a number

I have a table for Incidentes and I need to know the amount of tickets by analyst we are having, but we have a few analyst that only log in for special tickets and solve maybe 1 or 2 tickets in a month and are breacking my measure. I try adding a filter but it's not working

Incidents by Analysts =
VAR Last = max(CreatedDate[Date].[Date])
VAR First = min(CreatedDate[Date].[Date])
VAR QIncidents = CALCULATE(count(ITSM_Incidents[Key]), ITSM_Incidents[Createdwithouthour] >= First , ITSM_Incidents[Createdwithouthour] < Last)
VAR QAnalistas = CALCULATE(DISTINCTCOUNT(ITSM_Incidents[Assignee]),FILTER(ITSM_Incidents,count(ITSM_Incidents[Assignee])>100))


return QIncidents / QAnalistas
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    SebastianCostas , try

     

    Assignee cnt = count(ITSM_Incidents[Assignee])

    Incidents by Analysts =
    VAR Last = max(CreatedDate[Date].[Date])
    VAR First = min(CreatedDate[Date].[Date])
    VAR QIncidents = CALCULATE(count(ITSM_Incidents[Key]), ITSM_Incidents[Createdwithouthour] >= First , ITSM_Incidents[Createdwithouthour] < Last)
    VAR QAnalistas = Countx(FILTER(values(ITSM_Incidents[ITSM_Incidents[Assignee]]), [Assignee cnt ] >100) , ITSM_Incidents[Assignee])

     

  • SebastianCostas's avatar
    SebastianCostas
    4 years ago

    I did an small change to what you sent me and is working perfctly

    Incidents by Analysts =
    VAR Last = max(CreatedDate[Date].[Date])
    VAR First = min(CreatedDate[Date].[Date])
    VAR QIncidents = CALCULATE(count(ITSM_Incidents[Key]), ITSM_Incidents[Createdwithouthour] >= First , ITSM_Incidents[Createdwithouthour] < Last)
    VAR QAnalistas = countx(filter(VALUES(ITSM_Incidents[Assignee]),[Assigne cnt]>100),ITSM_Incidents[Assignee])

    return QIncidents / QAnalistas

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    SebastianCostas , try

     

    Assignee cnt = count(ITSM_Incidents[Assignee])

    Incidents by Analysts =
    VAR Last = max(CreatedDate[Date].[Date])
    VAR First = min(CreatedDate[Date].[Date])
    VAR QIncidents = CALCULATE(count(ITSM_Incidents[Key]), ITSM_Incidents[Createdwithouthour] >= First , ITSM_Incidents[Createdwithouthour] < Last)
    VAR QAnalistas = Countx(FILTER(values(ITSM_Incidents[ITSM_Incidents[Assignee]]), [Assignee cnt ] >100) , ITSM_Incidents[Assignee])

     

    • SebastianCostas's avatar
      SebastianCostas
      Frequent Visitor
      4 years ago

      Thank you very much, but i'm having an error with the last var:

       

    • SebastianCostas's avatar
      SebastianCostas
      Frequent Visitor
      4 years ago

      I did an small change to what you sent me and is working perfctly

      Incidents by Analysts =
      VAR Last = max(CreatedDate[Date].[Date])
      VAR First = min(CreatedDate[Date].[Date])
      VAR QIncidents = CALCULATE(count(ITSM_Incidents[Key]), ITSM_Incidents[Createdwithouthour] >= First , ITSM_Incidents[Createdwithouthour] < Last)
      VAR QAnalistas = countx(filter(VALUES(ITSM_Incidents[Assignee]),[Assigne cnt]>100),ITSM_Incidents[Assignee])

      return QIncidents / QAnalistas