Reply
Frequent Visitor
Posts: 13
Registered: ‎02-07-2018
Accepted Solution

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


Accepted Solutions
Community Support Team
Posts: 7,674
Registered: ‎05-02-2017

Re: Count first occurrence of string only

Hi @mhutchens81,

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Attachment
Community Support Team
Posts: 7,674
Registered: ‎05-02-2017

Re: Count first occurrence of string only

Hi @mhutchens81,

 

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

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post


All Replies
Super User
Posts: 1,830
Registered: ‎02-28-2017

Re: Count first occurrence of string only

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


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


Community Support Team
Posts: 7,674
Registered: ‎05-02-2017

Re: Count first occurrence of string only

Hi @mhutchens81,

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Attachment
Frequent Visitor
Posts: 13
Registered: ‎02-07-2018

Re: Count first occurrence of string only

Thanks 

Frequent Visitor
Posts: 13
Registered: ‎02-07-2018

Re: Count first occurrence of string only

[ Edited ]

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?

Community Support Team
Posts: 7,674
Registered: ‎05-02-2017

Re: Count first occurrence of string only

Hi @mhutchens81,

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Attachment
Frequent Visitor
Posts: 13
Registered: ‎02-07-2018

Re: Count first occurrence of string only

[ Edited ]

Thanks very much @v-jiascu-msft, you've saved me a bunch of time Smiley Happy Much appreciated. I've marked this as the solution.

Frequent Visitor
Posts: 13
Registered: ‎02-07-2018

Re: Count first occurrence of string only

One more thing @v-jiascu-msft - and apologies in advance if this goes against site rules or if I should create a new thread for this - what if I wanted to modify "Tickets touched 2" so that it looks for occurences of 'KT Solution Linked', but also another string, e.g. "Audit Trail"?

 

I have tried modifying your code to add a second variable, but then I get stuck on what to do with the results, e.g. adding the variables together, or using a nested 'IF' statement or a switch to work with the results, etc. I've included the code I'm playing with below as an example of that I'm trying (and failing to do).

 

Any help you can provide would be much appreciated!!

 

TICKETS_TOUCHED_NEW_NEW =
SUMX (
DISTINCT (
SELECTCOLUMNS (
ADDCOLUMNS (
'TestData',
"ifFirst",
VAR temp_comment_logged =
CALCULATE (
MIN ( TestData[DateCreated] ),
ALLEXCEPT ( TestData, 'TestData'[RefNum] ),
'TestData'[ActivityType] = "Comment Logged"
)
RETURN
VAR temp_status_changed =
CALCULATE (
MIN ( TestData[DateCreated] ),
ALLEXCEPT ( TestData, 'TestData'[RefNum] ),
'TestData'[ActivityType] = "Status Changed"
)
RETURN

            SWITCH (
             TRUE (),
                 [DateCreated] < temp_comment_logged || ISBLANK ( temp_comment_logged ), 1,
                 [DateCreated] < temp_status_changed || ISBLANK ( temp_status_changed ), 1,
                 0
                )
),
"RefNumTemp", [RefNum],
"ifFirstTemp", [ifFirst]
)
),
[ifFirstTemp]
)
Community Support Team
Posts: 7,674
Registered: ‎05-02-2017

Re: Count first occurrence of string only

Hi @mhutchens81,

 

How will you look for the other string? Count together with "Tickets touched 2"? If so, please try this one.

 

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

If not, I would suggest you create a new thread in this forum.

 

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 13
Registered: ‎02-07-2018

Re: Count first occurrence of string only

Thanks very much for your response again @v-jiascu-msft. I apologise for not properly documenting what my additional requirements were here:

To answer your follow-up question: Yes, I'm looking to count together with 'Tickets touched 2. But (this is the bit that in retrospect I wasn't with you clear about) I'd like to come up with a number that gives me a distinct count of tickets from the result, rather than counting both strings individually. So for example, if string 1 and string 2 are both present from the same analyst in the same ticket, it only counts as a single increment. But if string 1 and string 2 are present from the same analyst in separate tickets, they would count as two increments.

And this is still in the context of only wanting to count each string if those strings aren't already in the ticket, so if string 1 and string 2 are both present from the same analyst in the same ticket, but there is also already another string 1 in the same ticket from a different analyst that occurred previously, then that would only count as a single increment.