Search code examples
powerbidaxpowerbi-desktop

DAX Service recall rate measure

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

Edit

I realised it would be more helpful if I posted SQL code to generate data:

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

Solution

  • 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