Forum Discussion

king98027's avatar
king98027
Frequent Visitor
4 years ago
Solved

First Time Fix Rate

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%

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

4 Replies

    • king98027's avatar
      king98027
      Frequent Visitor
      4 years ago

      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's avatar
        king98027
        Frequent Visitor
        4 years ago

        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
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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