Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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").
Solved! Go to Solution.
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
Best Regards,
Dale
Hi @Anonymous,
I would suggest you create a new post in this forum. It's a new topic.
Best Regards,
Dale
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] )
Best Regards,
Dale
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
Best Regards,
Dale
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!!
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
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
Thanks very much @v-jiascu-msft, you've saved me a bunch of time 🙂 Much appreciated. I've marked this as the solution.
Imgur is blocked where I am, but I was going to suggest whether you have tried DISTINCTCOUNT?
Thanks Ross73312, I did try a couple variations of DISTINCTCOUNT but couldn't get the results I wanted.