cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JasserBI
Frequent Visitor

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!

2 REPLIES 2
JasserBI
Frequent Visitor

Hello @amitchandak,

 

Thank you for your response. It helped me to finally arrive to solution which consists in two steps:

 

1.- Creating a measure of the time difference in an AVERAGEX iterator.

2.- Using with CALCULATE that formula filtered with the rest of parameters to consider and the columns involved with the condition of that measure in each category >1min

 

amitchandak
Super User
Super User

@JasserBI , Have 4 measure like

 

SpeedResponse = SUMX(filter(factTable, Datediff( 'factTable'[ticket_creation_timestamp],'factTable'[ticket_response_timestamp] , minute) <1),
Datediff( 'factTable'[ticket_creation_timestamp],'factTable'[ticket_response_timestamp] , minute) )

of create one like


SpeedResponse = maxx(factTable, var _diff =Datediff( 'factTable'[ticket_creation_timestamp],'factTable'[ticket_response_timestamp] , minute)
Switch( True() ,
_diff < 1 , " < 1 Minute" ,
_diff < 5 , " 1 to 5 minutes" ,
_diff < 60 , " 5 to 60 minutes" , ">1 Hour"
) )

 

Consider Segmentation

 

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...


Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k



Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Learn Power BI P&L Statement || Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s ||
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!!! How to get Quick Help !!
Resources | Master Power BI !! Expertise Power BI !! Power BI For Tableau User !!

Helpful resources

Announcements
Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.