DAX Service recall rate measure

user_174892 0
Viewed 183

I'm struggling to write/calculate this measure in DAX. The definition of recall rate is count of repeat service bookings (count of distinct booking number, should be distinct anyway but just in case) for a customer, asset combination within a week (Closed on date, 7 day period). So I go out to fix a machine, if I get called out again to fix the same machine for the customer within a week that is then a recall of 1 (or more if i get called out multiple times within a week). I've highlighted the groups in different colours. Null Assets, Closed On and null booking number needs to be filtered out (this is done by inner join in SQL in below code, needs to be in DAX query) Thanks! EDIT : Sorry realised it would be more helpful if I posted sql code to generate data please see below :

SELECT
    FB.BookingNumber,
    FB.EngineerEmployeeID,
    FWO.ServiceAccountRecID AS Customer,
    FWO.AssetRecID AS Asset,
    FWO.ClosedOn
FROM dbo.FactWorkOrder AS FWO JOIN dbo.FactBooking AS FB ON FB.WorkOrderID = FWO.WorkOrderID
WHERE FWO.WorkOrderType = 'Breakdown'
    AND AssetRecID IS NOT NULL
    AND ClosedOn IS NOT NULL
ORDER BY BookingNumber

enter image description here

dax powerbi-desktop powerbi
2 Answers

It's most efficient if you first define a calculated column that gives the first CloseOn date for each Customer/Asset combination.

FirstClosed =
    CALCULATE (
        MIN ( WorkOrder[ClosedOn] ),
        ALLEXCEPT ( WorkOrder, WorkOrder[Customer], WorkOrder[Asset] )
    )

and then write a measure

TotalRecalls =
COUNTROWS (
    FILTER (
        WorkOrder,
        WorkOrder[ClosedOn] > WorkOrder[FirstClosed] &&
        WorkOrder[ClosedOn] < WorkOrder[FirstClosed] + 7
    )
)

However, you can do this all within a single measure if you prefer.

TotalRecalls =
VAR AddCol =
    ADDCOLUMNS (
        WorkOrder,
        "@FirstClosed",
            CALCULATE (
                MIN ( WorkOrder[ClosedOn] ),
                ALLEXCEPT ( WorkOrder, WorkOrder[Customer], WorkOrder[Asset] )
            )
    )
RETURN
    COUNTROWS (
        FILTER (
            AddCol,
            WorkOrder[ClosedOn] > [@FirstClosed] &&
            WorkOrder[ClosedOn] < [@FirstClosed] + 7
        )
    )

Either way, here's what this looks like used in a visual:

Matrix Visual

user_7652260

I would first create a "Booking Key" column:

Booking Key = [Customer] & "|" & [Asset] & "|" & WEEKNUM ( [ClosedOn] )

Then I would create a Measure to return a modified distinct count on the Booking Key:

# Repeat Service Bookings = 
VAR v_Count = DISTINCTCOUNT ( 'Table'[Booking Key] )
RETURN IF ( v_Count > 1, v_Count - 1 )

I would add # Repeat Service Bookings to the Visual Level Filters of your table visual, with the filter set to: is greater than 1.

user_17871370
Related