Forum Discussion

Daniel_carle's avatar
Daniel_carle
Icon for Helper I rankHelper I
2 years ago
Solved

First Time Fix (New Job Raised Within 14 days)

Hello,   I need to be able to to calculate first time fix but with an additional check please, I've been able to identify 'Repair' jobs that have been completed first time but my organisation want ...
Need Help
  • SamWiseOwl's avatar
    SamWiseOwl
    2 years ago

    COLUMN Repairwithin =
        VAR filttable =
            FILTER (
                Sheet1 --Remove all filters
                ,
                Sheet1[Job Category] = "Repair"
                    && Sheet1[Date] > EARLIER ( Sheet1[Date] )
                    && Sheet1[Date]
                        <= EARLIER ( Sheet1[Date] ) + 14
                    && Sheet1[ClientID] = EARLIER ( Sheet1[ClientID] )
            ) --Filter to only repairs that happen after current row and on or before 14 days in the future
        RETURN
            IF (
                Sheet1[Job Category] = "Repair"
                    && COUNTROWS ( filttable ) > 0,
                "Yes",
                "No"
            )
    --If the current row is a repair and the jobs is greater than 0 return Yes

SamWiseOwl's avatar
SamWiseOwl
Icon for Super User rankSuper User
2 years ago

Hello Daniel_carle ,

New Repair Job within 14 days =
var clientID = SELECTEDVALUE(Sheet1[ClientID])--capture current rows client
var clientDate = SELECTEDVALUE(Sheet1[Date])--capture current rows date
var filttable =
FILTER(
    ALLSELECTED(Sheet1)--Remove all filters
    ,Sheet1[Job Category] = "Repair" && Sheet1[Date] > clientdate && Sheet1[Date] <= clientdate + 14&& clientID = clientID)
    --Filter to only repairs that happen after current row and on or before 14 days in the future
RETURN IF(SELECTEDVALUE(Sheet1[Job Category]) = "Repair" && COUNTROWS(filttable) > 0, "Yes", "No")
--If the current row is a repair and the jobs is greater than 0 return Yes
 

Let me know how you get on 🙂

Daniel_carle's avatar
Daniel_carle
Icon for Helper I rankHelper I
2 years ago

Hello SamWiseOwl,

 

Thanks for the reply, my results are all returning as 'No', can you please share the revised pbix file

 

Thanks

 

Daniel

 

 

    • Daniel_carle's avatar
      Daniel_carle
      Icon for Helper I rankHelper I
      2 years ago

      Hello SamWiseOwl ,

       

      Thanks again, Is it possible that this can be a non measure column so I can see the results in the table rather then creating a visual to see it as I need to do another DAX query which links to these results

       

      Thanks

       

      Daniel

      • SamWiseOwl's avatar
        SamWiseOwl
        Icon for Super User rankSuper User
        2 years ago

        COLUMN Repairwithin =
            VAR filttable =
                FILTER (
                    Sheet1 --Remove all filters
                    ,
                    Sheet1[Job Category] = "Repair"
                        && Sheet1[Date] > EARLIER ( Sheet1[Date] )
                        && Sheet1[Date]
                            <= EARLIER ( Sheet1[Date] ) + 14
                        && Sheet1[ClientID] = EARLIER ( Sheet1[ClientID] )
                ) --Filter to only repairs that happen after current row and on or before 14 days in the future
            RETURN
                IF (
                    Sheet1[Job Category] = "Repair"
                        && COUNTROWS ( filttable ) > 0,
                    "Yes",
                    "No"
                )
        --If the current row is a repair and the jobs is greater than 0 return Yes