Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am new to PowerBI and I'd like to ask for a help with creating the following DAX function. Data set example:
Installation number Service ticket number ST date created ST date closed
1234 123313 1/1/2019 2/1/2019
123545 3/1/2019 3/1/2019
123333 1/9/2019 2/9/2019
For each installation numbers we have service ticket(s).
I want to measure first time fix understood as: If given service ticket was not followed up by another one for the same installation number in the period of 90 days, then we consider this service ticket to fall into first time fixed. So the service ticket 123545 was closed in January and the next one was in Sept, ergo it is First time fixed, whereas the 123313 service ticket, had a later one created just the next day, ergo it is not first time fixed. Could you please help with how to create this query?
Thank you
Lena
Did you ever get this to work? I'm in the same predicament. I tried using the formula but am getting bogus values.
Here was my separate thread:
https://community.powerbi.com/t5/Desktop/First-Time-Fix-Rate/m-p/2478575#M883071
hi, @Anonymous
You could try this formula to get your requirement:
Measure 2 = VAR nextcreatedate = CALCULATE ( MIN ( 'Table'[ST date created] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Installation number] = MAX ( 'Table'[Installation number] ) && MAX ( 'Table'[ST date closed] ) < 'Table'[ST date created] ) ) RETURN VAR days = IF ( nextcreatedate = BLANK (), DATEDIFF ( MAX ( 'Table'[ST date closed] ), TODAY (), DAY ), DATEDIFF ( MAX ( 'Table'[ST date closed] ), nextcreatedate, DAY ) ) RETURN IF ( days >= 90, "First time", "No" )
Result:
Best Regards,
Lin
How to create a First time fix %
I should add, that if a service ticket was closed in say May this year, but no new service ticket was created until today, then that ticket should also be counted as First time Fixed.
Thanks, Lena
Thank you very much Lin, it works great on a row level. However, the measure is formatted as text, (I have changed 'First time fix =1, and 'No" =0) to make it countable, I also added +0 at the end of the measure to be able to format it as whole number. I still am unable to sum up the results, and total appears as 1. Can you please help me to modify it so that I can get totals for specific time frames? I need to measure this KPI every month.
Thank you,
Lena
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |