First Time Fix Rate

[king98027]

Hello,

I'm trying to get a specific metric called "First Time Fix Rate" working within my report. I am a bit newer when it comes to DAX expressions and tried to follow a specific procedure I found here: https://community.powerbi.com/t5/Desktop/First-time-fix/m-p/800868

Unfortunately, I couldn't get it working with my tables, perhaps due to having serial numbers on a separate table. Essentially, I wanted the same thing. We have many instruments where we would do repairs for. Each repair is logged as a work order #. The critical dates I want to compare are the work order creation dates. Basically, if another work order was created for a specific serial number within 7 days, it would be logged as a "0", if not, logged as a "1". If it was the first work order logged for a system, it should be also logged as a "1" as well.

I would like to also be able to figure out the percentage of the first time fixed rate based off the total # of work orders. 

Any help on this would be much appreciated! 

Critical Tables:

1.) Customer Assets --> Serial Number

2.) Work Order --> Created On
3.) Work Order --> Work Order Number
 

Table 1 Goal:
Serial Number        Work Order Number         Created Date           First Time Fix

1111                       SWO00001                        4/1/2022                  1

1111                       SWO00005                        4/3/2022                  0

2222                       SWO00007                        4/4/2022                  1

2222                       SWO00008                        4/9/2022                  0 

2222                       SWO00009                        5/6/2022                  1

3333                       SWO000010                      5/6/2022                  1
3333                       SWO000050                      7/7/2022                  1

Table 2 Goal:
Serial Number        First Time Fix %
1111                       50%
2222                       66%

3333                       100%

[Anonymous · Accepted solution]

HI @king98027,

You can use the following measure formulas to get the fist time flag and rate:

First time Flag =
VAR currDate =
    MAX ( Table[Created Date] )
VAR last7dayCount =
    CALCULATE (
        COUNT ( Table[Work Order Number] ),
        FILTER (
            ALLSELECTED ( Table ),
            [Created Date] < currDate
                && [Created Date] >= currDate - 7
        ),
        VALUES ( Table[Serial Number] )
    )
RETURN
    IF ( last7dayCount > 1, 0, 1 )

first time rate =
VAR currSerialNumber =
    VALUES ( Table[Serial Number] )
VAR summary =
    SUMMARIZE (
        Table,
        [Serial Number],
        [Work Order Number],
        [Created Date],
        "Flag",
            VAR last7dayCount =
                COUNTX (
                    FILTER (
                        ALLSELECTED ( Table ),
                        [Serial Number] = EARLIER ( Table[Serial Number] )
                            && [Created Date] < EARLIER ( Table[Created Date] )
                            && [Created Date]
                                >= EARLIER ( Table[Created Date] ) - 7
                    ),
                    [Work Order Number]
                )
            RETURN
                IF ( last7dayCount > 1, 0, 1 )
    )
RETURN
    DIVIDE ( SUMX ( summary, [Flag] ), COUNTROWS ( summary ) )

Regards,

Xiaoxin Sheng

[amitchandak]

@king98027 , try a measure like

divide(countrows(filter(Table, Table[First time fix] =1)), countrows(Table))

[king98027]

Thanks, that looks like it would take care of my 2nd goal, but I'm still having a lot of difficulty trying to get to the 1st goal of implementing a first time fix value for each work order.  

[king98027]

So I was able to make a little headway with Goal #1. Basically, I added a new column to the 'Work Order' Table, which went like this

Serial Number = RELATED('Customer Assets'[Serial Number])

However, the data doesn't seem to be calculating as expected when using 14 days rather than the example's 90 day requirement. I'm not sure exactly how it's defining what should be "1" or "0". 

I quickly realize I'll have to filter for only 'Repair' work order types orders somehow. 

The table using the new measure is below:

Measure in place: 

First time3 =

VAR nextcreatedate =

CALCULATE (

MIN ( 'Work Order'[Created On] ),

FILTER (

ALLSELECTED ( 'Work Order' ),'Work Order'[Serial Number] = MAX ( 'Work Order'[Serial Number] )

&& MAX ('Work Order'[Work Order Completed] ) < 'Work Order'[Created On]

)

)

RETURN

VAR days =

IF (

nextcreatedate = BLANK (),

DATEDIFF ( MAX ( 'Work Order'[Work Order Completed] ), TODAY (), DAY ),

DATEDIFF ( MAX ( 'Work Order'[Work Order Completed] ), nextcreatedate, DAY )

)

RETURN

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