topic Re: DAX formula to count ticket status in DAX Commands and Tips

DAX formula to count ticket status

APéczely — Sun, 20 Oct 2024 14:43:29 GMT

Dear Community,
I have the following output from a ticketing service shown in the table below:

ID	Current status	Created	Current Status Date	Open	In-progress	Contested	For Review	Closed
10001	For review	2024.01.01	2024.02.01	2024.01.01	2024.01.05		2024.02.01
10002	Open	2024.01.18	2024.01.18	2024.01.18
10003	In-progress	2024.01.25	2024.01.31	2024.01.25	2024.01.31	2024.01.28
10004	Contested	2024.01.28	2024.02.03	2024.01.28		2024.02.03
10005	Closed	2024.01.16	2024.02.05	2024.01.16			2024.01.31	2024.02.05

The default status of a ticket at creation is "Open". The last status of a ticket is "Closed". In between, the ticket can have any status, but for simplicity we assume that each status is used only once.

Date format is "yyyy.mm.dd".

What is the most effective DAX solution to create a time history of the ticket status counts in Power BI as shown in the following bar chart?

Any help would be appreciated.

Re: DAX formula to count ticket status

Greg_Deckler — Sun, 20 Oct 2024 14:49:24 GMT

APéczely In Power Query Editor, select your first 2 columns, right click and Unpivot Other Columns. The rest is trivial at that point.

Re: DAX formula to count ticket status

APéczely — Sun, 20 Oct 2024 15:07:40 GMT

Greg_Deckler, As a matter of fact, it is all but obvious to me after unpivoting the columns you mentioned. How do I count a given status for a given date when I only have the date of status changes?

Re: DAX formula to count ticket status

Rupak_bi — Mon, 21 Oct 2024 10:56:44 GMT

Hi APéczely

hope below visual displaying right.

The approach is as below

1. unpivot all 5 different stages in power query.

2. create a calender table using min and max date of all the stages

date =

var Min_date = CALCULATE(min('Table'[Value]),all('Table'))

var Max_date = CALCULATE(max('Table'[Value]),all('Table'))

return

CALENDAR(Min_date,Max_date)

3. create a calculated table as below

cal_tab =

SUMMARIZECOLUMNS('date'[Date],'Table'[ID],"max date",CALCULATE(max('Table'[Value]),'Table'[Value]<=max('date'[Date])))

5. Add a new column in the above table

new Stat = LOOKUPVALUE('Table'[Attribute],'Table'[Value],cal_tab[max date],'Table'[ID],cal_tab[ID])

6. now create the stack bar chart using the date (X axis), new state (ledgent) and count of new state (Y).
Plz let me know if this works.

Re: DAX formula to count ticket status

Greg_Deckler — Mon, 21 Oct 2024 15:22:51 GMT

APéczely I was referring to Open Tickets, should have included the reference. Open Tickets - Microsoft Fabric Community This may need to be tweaked based on your particular requirements but the advantage here is that this approach is dynamic.

PBIX is attached below signature. This involved a calculated column ( which you could make part of the measure if you wanted )

Next = VAR __ID = [ID] VAR __Status = [Attribute] VAR __Date = [Value] VAR __Table = FILTER( 'Table', [ID] = __ID && NOT( [Attribute] IN { __Status, "Created", "Current Status Date" } ) && [Value] >= __Date ) VAR __MinDate = MINX( __Table, [Value] ) VAR __Result = IF( __MinDate = BLANK(), MAX( 'Dates'[Date] ), __MinDate ) RETURN __Result

and the Open Tickets measure:

Tickets = VAR __Table = SELECTCOLUMNS( FILTER( GENERATE( 'Table', 'Dates' ), [Date] >= [Value] && [Date] < [Next] ), "ID",[ID], "Date",[Date] ) VAR __Table1 = GROUPBY( __Table,[ID],"Count",COUNTX( CURRENTGROUP(),[Date] )) VAR __Result = COUNTROWS( __Table1 ) RETURN __Result

Re: DAX formula to count ticket status

Rupak_bi — Mon, 21 Oct 2024 18:13:02 GMT

Hi, if my solution works, please accept the reply as solution.

Truly appreciate your kudos.

Re: DAX formula to count ticket status

APéczely — Mon, 21 Oct 2024 18:53:01 GMT

Rupak_bi : Almost! The concept of your proposed DAX code works pretty well! But the days are shifted by +1 day at the status change days. For example ticket ID #1001 is open since 2024.01.01, but the start is 2024.01.02 on the chart. Also, the In-progress status is set on 2024.01.05, but the chart shows from 2024.01.06.
I'm trying to find a way to shift everything one day back!

Re: DAX formula to count ticket status

Rupak_bi — Mon, 21 Oct 2024 19:16:59 GMT

Hi,

Modify below formula and remove the "=" sign and check.

date",CALCULATE(max('Table'[Value]),'Table'[Value]<=max('date'[Date])))

Re: DAX formula to count ticket status

APéczely — Mon, 21 Oct 2024 20:33:31 GMT

Rupak_bi: Unfortunately, removing the "=" sign doesn't change the result at all, there is still +1 day shift.

Re: DAX formula to count ticket status

APéczely — Mon, 21 Oct 2024 20:59:27 GMT

Greg_Deckler: Thanks a lot! This problem intrigued me since my first Power BI report, as much as you were struggling with your Open tickets DAX code. I still need time to understand how the ticket measure works, but this is definitely a solution!

Re: DAX formula to count ticket status

Rupak_bi — Tue, 22 Oct 2024 06:09:28 GMT

Hi,

Just now I checked my approach and found it is working correctly and there is no date shift. please refer below.

Re: DAX formula to count ticket status

APéczely — Tue, 22 Oct 2024 09:37:23 GMT

Rupak_bi I can't achieve the same! Which date column do you use for X axis: 'cal_tab'[Date]?
This is my table:

The new Stat column formula:

This is my cal_tab:

ID #10001 starts with Open status on 02 Jan instead of 01 Jan and switched to In-progress on 06 Jan instead of 05 Jan.

Re: DAX formula to count ticket status

Rupak_bi — Tue, 22 Oct 2024 10:47:55 GMT

Yes . the date column from table cal_tab is used as X -axis. and new stat used as ledgend. just check it out

Please check your date table formula. the date should start from first january

Re: DAX formula to count ticket status

APéczely — Tue, 22 Oct 2024 13:54:26 GMT

Rupak_bi : My date table is fine. If I comment out the 'cal_tab'[max date] column, the 'cal_tab'[date] column is fine. But once I switch it back, 01 January disappears from the 'cal_tab'[date] column and the dates of status changes are incorrect:

I run Power BI Desktop version: 2.137.751.0 64-bit (October 2024).

I share my file in case you have time to have a look on it and spot something I did wrong: Status test.pbix

Re: DAX formula to count ticket status

Rupak_bi — Tue, 22 Oct 2024 17:16:35 GMT

Hi APéczely ,

I will check your file in some time, meanwhile, I can see, your values have time stamp as well and due to this you are not getting right output. So you need to create a calculated column in the fact table as "new values" and only dates to be extracted from values. Then this new column to be used to create calc_tab. Hope this will solve the issue.

Re: DAX formula to count ticket status

APéczely — Wed, 23 Oct 2024 04:15:55 GMT

Rupak_bi You were right! 'table'[Value] should be of type date instead of datetime! Thank you for your solution!