cancel
Showing results for
Did you mean:
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
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

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

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors