Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

2 ACCEPTED SOLUTIONS

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

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

Hi @Anonymous,

 

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

10 REPLIES 10
v-jiascu-msft
Employee
Employee

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

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.
Anonymous
Not applicable

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?

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

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.
Anonymous
Not applicable

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]
)

Hi @Anonymous,

 

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.
Anonymous
Not applicable

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.

Hi @Anonymous,

 

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.
Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

Thanks 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.