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

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 !!

Announcements

#### 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

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 - 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.

Top Solution Authors
Top Kudoed Authors