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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

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.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.