Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Count first occurrence of string only

I have a db with a few million ITSM tickets. Each of those tickets has a Reference Number, as well as some Activity data, datestamps, plus the name of the person who has performed the activity.

I want to be able to count how many times each person has performed a 'KT Solution Linked' activity on each ticket, but only if there isn't already a 'KT Solution Linked' activity in the ticket. I've mocked up a table with some example data, the measures I'm currently using ("Old Stats") as well as what I might expect to see "New Stats").

 

 https://imgur.com/a/Z2bQfPq

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Anonymous,

     

    I created a new [Tickets touched 2]. Please check it out in the attachment.

    Tickets touched 2 =
SUMX (
    DISTINCT (
        SELECTCOLUMNS (
            ADDCOLUMNS (
                'Table1',
                "ifFirst",
                VAR temp =
                    CALCULATE (
                        MIN ( Table1[ActivityDateStamp] ),
                        ALLEXCEPT ( Table1, 'Table1'[RefNum] ),
                        'Table1'[Activity] = "KT Solution Linked"
                    )
                RETURN
                    IF ( [ActivityDateStamp] < temp || ISBLANK ( temp ), 1, 0 )
            ),
            "RefNumTemp", [RefNum],
            "ifFirstTemp", [ifFirst]
        )
    ),
    [ifFirstTemp]
)

    Result = DIVIDE([Measure 2], [Tickets Touched 2]) * 100

    Count-first-occurrence-of-string-only2

     

    Best Regards,
    Dale

  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Anonymous,

     

    I would suggest you create a new post in this forum. It's a new topic. 

     

    Best Regards,
    Dale

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Imgur is blocked where I am, but I was going to suggest whether you have tried DISTINCTCOUNT?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks 

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Anonymous,

     

    Please try this measure and download the demo in the attachment.

    Measure 2 =
SUMX (
    ADDCOLUMNS (
        'Table1',
        "ifFirst", IF (
            [ActivityDateStamp]
                = CALCULATE (
                    MIN ( Table1[ActivityDateStamp] ),
                    ALLEXCEPT ( Table1, 'Table1'[RefNum] ),
                    'Table1'[Activity] = "KT Solution Linked"
                ),
            1,
            0
        )
    ),
    [ifFirst]
)

     Count-first-occurrence-of-string-only

     

    Best Regards,
    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks very much v-jiascu-msft, that works a treat. Much appreciated.

       

      As a further refinement, what I'd like to be able to do is create a 'Link %' measure, which would be calculated as ([Measure 2] / [Tickets Touched] * 100). But the logic behind 'Tickets Touched' would need to change from "DISTINCTCOUNT of RefNum" to "DISTINCTCOUNT of RefNum, only where there hasn't been a previous occurrence of 'KT Solution Linked'". Is that something you might be able to assist with?

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        Hi Anonymous,

         

        I created a new [Tickets touched 2]. Please check it out in the attachment.

        Tickets touched 2 =
SUMX (
    DISTINCT (
        SELECTCOLUMNS (
            ADDCOLUMNS (
                'Table1',
                "ifFirst",
                VAR temp =
                    CALCULATE (
                        MIN ( Table1[ActivityDateStamp] ),
                        ALLEXCEPT ( Table1, 'Table1'[RefNum] ),
                        'Table1'[Activity] = "KT Solution Linked"
                    )
                RETURN
                    IF ( [ActivityDateStamp] < temp || ISBLANK ( temp ), 1, 0 )
            ),
            "RefNumTemp", [RefNum],
            "ifFirstTemp", [ifFirst]
        )
    ),
    [ifFirstTemp]
)

        Result = DIVIDE([Measure 2], [Tickets Touched 2]) * 100

        Count-first-occurrence-of-string-only2

         

        Best Regards,
        Dale