topic DAX Measures for counting time differencies by speed category in DAX Commands and Tips

DAX Measures for counting time differencies by speed category

JasserBI — Sun, 21 Aug 2022 20:07:47 GMT

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!

Re: DAX Measures for counting time differencies by speed category

amitchandak — Mon, 22 Aug 2022 03:56:49 GMT

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/1387187#M626

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

Re: DAX Measures for counting time differencies by speed category

JasserBI — Tue, 30 Aug 2022 11:06:39 GMT

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