DAX Service recall rate measure

Question

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

Do you expect your visual to look like the Excel screenshot? If not, what would it look like? — Alexis Olson
Hi @AlexisOlson it would simply be those total amounts split by engineer name ,or they may also want the amount split by customer name or asset. i.e. if i have a bar chart i would expect to see a few engineer names with the totals split among them or for customer, the totals would be split by two customers as there's two different customer id's in the results... primarily they're interested in recall rate per engineer. — jhowe
Total of what? Do you mean the maximum of the Recall Rate column in your image for that grouping (8, 2, 3)? — Alexis Olson
Yes I want the totals for the group split, so for combination customer 530041, asset 19987 (green group) engineer 797 i count 4 and for engineer 428 i count 4. At customer/asset level it is 8 for that group (green group) as shown in the screenshot. I'll simply be putting engineer name in a stacked bar chart against recall measure. It would be nice to show customer against recall i.e. 8 for green group in another visual if possible but mainly concerned with getting the split for engineers correct. — jhowe
Even though engineer 428 for booking number 288 is his first visit, it's still counted as a recall because it's per customer/asset. For the orange group engineer 797 I count 2 and engineer 428 I count 1. — jhowe

Alexis Olson Alexis Olson · Accepted Answer · 2021-03-08T23:45:29

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:

DAX Service recall rate measure

2 Answers