Acummulated tickets along time - Show acummulated after today - Cumulative Totals
Hi guys, I need your help... I'm trying to show the accumulated open tickets along the months, but on visuals they extend until december. I would like to see until the current month, I have try almost everything, but not worked, even the ChatGPT could give me a good answer. You are my last hope. I use this measure: CALCULATE ( CALCULATE ( COUNTROWS ( f_Suporte ), FILTER ( ALL ( f_Suporte[##Related Status_Cliente] ), f_Suporte[##Related Status_Cliente] = "Ativo" ), FILTER ( ALL ( f_Suporte[##StatusTicket] ), f_Suporte[##StatusTicket] = "ABERTO" ) ), FILTER ( ALL ( Date_Table_TR[Date] ), Date_Table_TR[Date] <= MAX ( Date_Table_TR[Date] ) ) )
Calculate open tickets and the one with max duration at the moment of given dates
I have read some post related to ticket systems, none of them have helped me. I have a common dataset called ITS, with a Key, created_date and resolved_date; if the ticket is unsolved, resolved_date is blank. I calculate the number of open tickets in a set of dates. For example, every Thursday since the ticket systems started. The dates table would be like this Thursdays = FILTER('Date',WEEKDAY('Date'[Date])=5 || 'Date'[Date]=TODAY()) And I calculate the number of open tickets like this: Open = CALCULATE ( COUNT ( ITS[Key]), FILTER(ALL(ITS), ITS[Created] <=[Date] && ( ITS[Resolved] > [Date] || ISBLANK ( ITS[Resolved] ) ) ) ) But I also need to know which ticket had more time opened in every given date, and it's duration. I am an amateur in DAX, have tried some approaches, all of the unsuccessful. I hope some of you can help me. Thanks for the time!DAX Measures for counting time differencies by speed category
Hello Community, I am trying to calculate through only DAX Measures the quantity of tickets being responded in 4 categories of speed (Difference between the timestamp of ticket creation minus the timestamp of ticket_start_to_resolve) : <1 minute, 1 to 5 minutes, 5 to 60 minutes, >1 Hour What I have tried until now 🙂 1.- Perform a measurement called SpeedResponse SpeedResponse = SUMX(factTable, 'factTable[ticket_response_timestamp] - 'factTable[ticket_creation_timestamp]) 2.- (integration of 2.- and 3.- former steps into one Measurement). <1 min_total = //I know the following is not working, but in that way you will see the idea behind calculating the total of rows (tickets) with specific speed so that I can count them and then filter that total ammount with the additional filters later. var _lessThan1Minute = COUNTX(factTable, CALCULATE([SpeedResponse], IF(AND(HOUR([SpeedResponse])=0, MINUTE([SpeedResponse])<1),1,0)) ) var _lessthan1MinuteFiltered = CALCULATE(_lessThan1Minute), FILTER(ALL(factTable[ticketOrigin]), ALL(factTable[ticketOrigin]="outsourcing") return _lessthan1MinuteFiltered Thank you so much in advance community!Creation of DAX measures instead of calculated columns for calculating time speed % categories
Hello All, I am trying to calculate through only DAX Measures the quantity of tickets being responded in 4 categories of speed (Difference between the timestamp of ticket creation minus the timestamp of ticket_start_to_resolve) : <1 minute, 1 to 5 minutes, 5 to 60 minutes, >1 Hour Right now it is calculated in the following steps: 1.- Calculated the difference of timestamps from the two factTable timestamps previously described and named that column as "responseSpeed" -> Performed in PowerQuery 2.- Creation of 4 calculated conditional columns from the previous "responseSpeed" calculation and marked each row as 1 if it is categorized as <1 minute, 1 to 5 minutes, 5 to 60 minutes or >1 Hour ->Perfomed too in Power Query 3.- Calculation of measures for totals by category with additional specific filters related to the business thanks to CALCULATE function and FILTERS from other specific columns. ->Those measures are calculated in DAX and they are calculated like CALCULATED(SUM(), filtered(all(columns, columns="value") of the previous calculated columns in Power Query and filtered. 4.- Finally, those 4 measures in DAX are divided by the total quantity of tickets created so that We have the calculation for every % from each category. ->Performed in DAX and examples like: % <1min = [<1min] / [Total numbers of tickets] So, what is the deal? I am trying to integrate steps 1.- and 2.- in DAX Measures too in order to free space for the .pbix size and later on the model in the PowerBI service. Calculated columns are not considered as the space occupied is higher even than the steps performed What I have tried until now 🙂 1.- Perform a measurement called SpeedResponse SpeedResponse = SUMX(factTable, 'factTable[ticket_response_timestamp] - 'factTable[ticket_creation_timestamp]) 2.- (integration of 2.- and 3.- former steps into one Measurement). <1 min_total = //I know the following is not working, but in that way you will see the idea behind calculating the total of rows (tickets) with specific speed so that I can count them and then filter that total ammount with the additional filters later. var _lessThan1Minute = COUNTX(factTable, CALCULATE([SpeedResponse], IF(AND(HOUR([SpeedResponse])=0, MINUTE([SpeedResponse])<1),1,0)) ) var _lessthan1MinuteFiltered = CALCULATE(_lessThan1Minute), FILTER(ALL(factTable[ticketOrigin]), ALL(factTable[ticketOrigin]="outsourcing") return _lessthan1MinuteFiltered Thank you so much in advance community!
