First time fix

Question

Hello,I am new to PowerBI and I'd like to ask for a help with creating the following DAX function. Data set example:&nbsp;Installation number&nbsp; &nbsp; &nbsp; &nbsp; Service ticket number&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ST date created&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ST date closed1234&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 123313&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1/1/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2/1/2019&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 123545&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3/1/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3/1/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 123333&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1/9/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2/9/2019&nbsp;For each installation numbers we have service ticket(s).&nbsp;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?&nbsp;Thank youLena&nbsp;&nbsp;

v-lili6-msft · Answer

hi,&nbsp;Anonymous&nbsp;
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] )
                &amp;&amp; MAX ( 'Table'[ST date closed] ) &lt; '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 &gt;= 90, "First time", "No" )
Result:

&nbsp;
Best Regards,
Lin

anonymous · Answer

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

anonymous · Answer

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

First time fix =

VAR nextcreatedate =

CALCULATE (

MIN ( Services[Date Created] ),

FILTER (

ALLSELECTED ( 'Services' ),'Services'[Installation] = MAX ( 'Services'[Installation] )

&& MAX ('Services'[Date Closed] ) < 'Services'[Date Created]

)

RETURN

VAR days =

IF (

nextcreatedate = BLANK (),

DATEDIFF ( MAX ( 'Services'[Date Closed] ), TODAY (), DAY ),

DATEDIFF ( MAX ( 'Services'[Date Closed] ), nextcreatedate, DAY )

)

RETURN

IF ( days >= 90, "1", "0" )+0

king98027 · Answer

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

sau1111 · Answer

How to create a First time fix %

Forum Discussion

First time fix

5 Replies

Related content

Actual quadruple how to fix?

Date Field Time Zone Fix

Time vs Time Scatter Chart

Fixed Index Column

First Time Fix