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

amitchandak's avatar
amitchandak
Super User
4 years ago

king98027 , try a measure like

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

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