topic Re: Open Incidents per day in DAX Commands and Tips

Open Incidents per day

Thogen — Mon, 03 Jun 2024 10:21:17 GMT

Dear All,

I'm having some trouble with creating a measure which check for the number of open incidents.
I want to place this measure in a matrix or chart with Date on the rows.

My thought process has been the following:
I have a fact table called Raw_Transaction which contains status changes for incidents such as creating, updating, and closing. An IncidentId can appear multiple times in this table, and an incident ID can be opened and closed multiple times. So an incident ID will have multiple records. This table is ~15.000.000 rows.
To make it a bit easier for myself (I think...), I've created a column which is a basically a RANK number of what number this transaction is for an incidentID, called "CountOfTransaction". E.g. incidentID = 1 and CountOftransaction = 6, that means it's the 6th status change/transaction for incidentID 1.
To get each incidents latest status for a date, my idea was to:
Create vTable with CALCULATETABLE
Filter this table using DATESINPERIOD to adjust the daterange when the measure is in a visual.
Then Create a column with ADDCOLLUMS which checks for each incident ID's the highest "CountOftransaction" in the context that has been adjusted by DATESINPERIOD.
FILTER that in a ROW context to say that the CountOFtransaction needs to be equal the [@MaxTransaction]
Count the IncidentID's where that result is not "Closed".
Currently I have the expression below (hope this illustrates what I want to accomplish) but it’s not working as I'd hoped. Would it be possible to comment on how to create the correct calculation? If you can comment on 'why' it's not working that be appreciated as well! Thanks in advance for the assistance!

Open Incidents = VAR MaxDate = MAX('_Date'[Date]) VAR MinDate = CALCULATE( MIN( '_Date'[Date] ), REMOVEFILTERS() ) VAR NoOFDays = ABS(Maxdate) - ABS(MinDate) VAR Period = DATESINPERIOD( _Date[Date], Maxdate, -NoOFDays, DAY ) VAR _CalcTable = CALCULATETABLE( Raw_Transactions, Period ) VAR _vTable = ADDCOLUMNS( _CalcTable, "@MaxTransaction", VAR Result = CALCULATE( MAX( Raw_Transactions[CountOfTransaction] ), ALLEXCEPT( Raw_Transactions, Raw_Transactions[IncidentId] ) ) RETURN IF( Result = Raw_Transactions[CountOfTransaction], Result ) ) RETURN CALCULATE( COUNTX( _vTable, [@MaxTransaction] ), Raw_Transactions[TransactionType] <> "Closed" )

Re: Open Incidents per day

Joe_Barry — Mon, 03 Jun 2024 10:57:12 GMT

Hi Thogen

Create a Running Total

Incidents RT = VAR MaxDate = MAX ( _Date[Date] ) RETURN CALCULATE ( DISTINCTCOUNT(Incidents[IncidentID]), KEEPFILTERS ( 'Incidents'[DateUTC] <= MaxDate && 'Incidents'[DateUTC] >= MaxDate && Incidents[TransactionType] <> "Closed") , ALL ( '_Date'))

Joe

Re: Open Incidents per day

Thogen — Mon, 03 Jun 2024 13:04:00 GMT

Hi Joe_Barry ,

Thanks for assisting,

The result from your measure seems (not 100% sure) to only be number of status changes for the given day.

Below is the result I'm looking for. #Open incident = 43.

Is there some way to achieve this result?

Thanks in advance!

Re: Open Incidents per day

Joe_Barry — Mon, 03 Jun 2024 13:16:46 GMT

Hi Thogen

Try replacing DISTINCTCOUNT with COUNT

Re: Open Incidents per day

Thogen — Mon, 03 Jun 2024 13:26:08 GMT

Slight impact but it doesn't seem to impact the result the way I want.

Re: Open Incidents per day

Joe_Barry — Mon, 03 Jun 2024 13:46:09 GMT

Sorry Thogen

Maybe try replacing >= MaxDate with > MaxDate

If that doesn't work, add a custom column to show the last date before the new entry

LastStatusEndDate = VAR CurrentIncID = Incidents[IncidentID] RETURN CALCULATE( MAX(Incidents[DateUTC]), FILTER( Incidents, Incidents[IncidentID] = EARLIER(Incidents[IncidentID]) && Incidents[DateUTC] < EARLIER(Incidents[DateUTC] ) ))

Then redo the measure

Incidents RT = VAR MaxDate = MAX ( DIM_Date[Date] ) RETURN CALCULATE ( DISTINCTCOUNT(Incidents[IncidentID]), KEEPFILTERS ( 'Incidents'[DateUTC] <= MaxDate && 'Incidents'[LastStatusEndDate] > MaxDate && Incidents[TransactionType] <> "Closed") , ALL ( 'DIM_Date'))

If this doesn't work, Can you provide the Data? It doesn't look to sensitve

Joe

Re: Open Incidents per day

Thogen — Wed, 05 Jun 2024 11:25:21 GMT

Joe_Barry

If tried a couple of times to post some data but it keeps deleting my post.

Your last solution only returns blanks

EDIT: It's seems to only be deleted when I post it as a reply. I've posted it regularly now and it seems to not have been deleted.

Re: Open Incidents per day

Thogen — Wed, 05 Jun 2024 08:31:28 GMT

IncidentId	DateUTC	TransactionType	CountOfTransaction
1	02/Jan/23	Created	1
1	02/Jan/23	Closed	2
5	02/Jan/23	Created	1
18	03/Jan/23	Created	1
1	03/Jan/23	Updated	3
1	03/Jan/23	Closed	4
17	03/Jan/23	Created	1
3	03/Jan/23	Created	1
14	03/Jan/23	Created	1
9	04/Jan/23	Created	1
21	04/Jan/23	Created	1
21	04/Jan/23	Closed	2
8	05/Jan/23	Created	1
26	05/Jan/23	Created	1
29	05/Jan/23	Created	1
30	05/Jan/23	Created	1
40	05/Jan/23	Created	1
9	05/Jan/23	Closed	2
29	05/Jan/23	Closed	2
56	06/Jan/23	Created	1
24	06/Jan/23	Created	1
33	06/Jan/23	Created	1
44	06/Jan/23	Created	1
48	06/Jan/23	Created	1
51	06/Jan/23	Created	1
44	06/Jan/23	Closed	2
71	07/Jan/23	Created	1
14	07/Jan/23	Closed	2
26	07/Jan/23	Closed	2
38	07/Jan/23	Created	1
30	07/Jan/23	Closed	2
57	07/Jan/23	Closed	2
57	07/Jan/23	Created	1
72	08/Jan/23	Created	1
74	08/Jan/23	Created	1
3	08/Jan/23	Closed	2
5	08/Jan/23	Closed	2
35	08/Jan/23	Closed	2
40	08/Jan/23	Closed	2
48	08/Jan/23	Closed	2
14	08/Jan/23	Updated	3
35	08/Jan/23	Created	1
11	08/Jan/23	Created	1
13	08/Jan/23	Created	1
19	08/Jan/23	Created	1
20	08/Jan/23	Created	1
49	08/Jan/23	Created	1
62	08/Jan/23	Created	1
15	09/Jan/23	Created	1
6	09/Jan/23	Created	1
10	09/Jan/23	Created	1
23	09/Jan/23	Created	1
27	09/Jan/23	Created	1
31	09/Jan/23	Created	1
50	09/Jan/23	Created	1
59	09/Jan/23	Created	1
65	09/Jan/23	Created	1
87	09/Jan/23	Created	1
88	09/Jan/23	Created	1
94	09/Jan/23	Created	1
13	09/Jan/23	Closed	2
15	09/Jan/23	Closed	2
17	09/Jan/23	Closed	2
24	09/Jan/23	Closed	2
49	09/Jan/23	Closed	2
51	09/Jan/23	Closed	2
56	09/Jan/23	Closed	2
72	09/Jan/23	Closed	2
87	09/Jan/23	Closed	2
3	09/Jan/23	Updated	3
15	09/Jan/23	Updated	3
17	09/Jan/23	Updated	3
26	09/Jan/23	Updated	3
51	09/Jan/23	Updated	3
15	09/Jan/23	Closed	4
96	10/Jan/23	Created	1
28	10/Jan/23	Created	1
41	10/Jan/23	Created	1
42	10/Jan/23	Created	1
43	10/Jan/23	Created	1
47	10/Jan/23	Created	1
52	10/Jan/23	Created	1
54	10/Jan/23	Created	1
63	10/Jan/23	Created	1
76	10/Jan/23	Created	1
80	10/Jan/23	Created	1
84	10/Jan/23	Created	1
4	10/Jan/23	Created	1
102	10/Jan/23	Created	1
109	10/Jan/23	Created	1
18	10/Jan/23	Closed	2
27	10/Jan/23	Closed	2
41	10/Jan/23	Closed	2
42	10/Jan/23	Closed	2
50	10/Jan/23	Closed	2
62	10/Jan/23	Closed	2
35	10/Jan/23	Updated	3
3	10/Jan/23	Closed	4
35	10/Jan/23	Closed	4
51	10/Jan/23	Closed	4

Re: Open Incidents per day

Thogen — Wed, 05 Jun 2024 11:24:12 GMT

Open Incidents = VAR MaxDate = MAX('_Date'[Date]) VAR MinDate = CALCULATE( MIN( '_Date'[Date] ), REMOVEFILTERS() ) VAR NoOFDays = ABS(Maxdate) - ABS(MinDate) VAR Period = DATESINPERIOD( _Date[Date], Maxdate, -NoOFDays, DAY ) VAR _vTable = CALCULATETABLE( ADDCOLUMNS( Raw_Transactions, "@MaxTransaction", VAR Result = CALCULATE( MAX( Raw_Transactions[CountOfTransaction] ), ALLEXCEPT( Raw_Transactions, Raw_Transactions[IncidentId] ), Period ---Statement for the daterange needed to be added here--- ) RETURN IF( Result = Raw_Transactions[CountOfTransaction], Result ) ), Period, --Statement for the daterange needed to be added here-- Raw_Transactions[TransactionType] <> "Closed" ---Filter for not Closed needed to be here--- ) RETURN COUNTX( _vTable, [@MaxTransaction] )

I've found a solution which gives me the results I'm looking for.

Performance wise it's not great, but it's good enough for now.

Thanks for the assistance anyway!