Forum Discussion
DAX formula to count ticket status
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.
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'))returnCALENDAR(Min_date,Max_date)3. create a calculated table as belowcal_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 tablenew 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.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 __Resultand 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
15 Replies
- Rupak_bi
Super User
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'))returnCALENDAR(Min_date,Max_date)3. create a calculated table as belowcal_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 tablenew 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.- Rupak_bi
Hi, if my solution works, please accept the reply as solution.
Truly appreciate your kudos.
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!
- Greg_Deckler
Community Champion
APéczely In Power Query Editor, select your first 2 columns, right click and Unpivot Other Columns. The rest is trivial at that point.
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?
- Greg_Deckler
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 __Resultand 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
- Rupak_bi
Hi,
Just now I checked my approach and found it is working correctly and there is no date shift. please refer below.
- Rupak_bi
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
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
- Rupak_bi
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.
