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, Mar 09 '21 at 14:34
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, Mar 09 '21 at 22:37
Total of what? Do you mean the maximum of the Recall Rate column in your image for that grouping (8, 2, 3)? — Alexis Olson, Mar 09 '21 at 22:48
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, Mar 10 '21 at 09:31
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, Mar 10 '21 at 09:40

Alexis Olson · Accepted Answer · 2021-03-10T18:55:09.453

2

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:

edited Mar 10 '21 at 18:55

answered Mar 08 '21 at 23:45

Alexis Olson

38,724
7
42
64

Hi, thanks for your reply. I want to add this as a measure as mentioned in the title. A number that can be split in a visual by either engineer, customer or asset, or a combination of the three. – jhowe Mar 09 '21 at 09:58
Check if my edit is closer to what you're after. – Alexis Olson Mar 10 '21 at 15:32
Thanks Alexis, this looks close however the totals aren't quite right, if you check my comments above I would expect a count of 4 for each engineer (8 total) for customer 530041/asset 19987. For orange group engineer 797 I count 2 and engineer 428 I count 1 (3 total), you will see this by looking at the engineer id's in original screenshot... I have a masked version of my dataset if that helps but don't want to post my one drive link in public as it has my name in it... and doesn't look like I can send a private message on stack overflow. – jhowe Mar 10 '21 at 18:08
That's exactly what my table is showing. Do you not see the two 4s in the 19987 row corresponding to the employee columns? – Alexis Olson Mar 10 '21 at 18:12
Yes i see now, sorry I was reading across the top row to 9 in the top right. Is it including row for booking number 302? This would be incorrect... as it's different asset and not a recall, I would still expect a count of 8 total for customer 530041... – jhowe Mar 10 '21 at 18:19
Sorry let me clarify, if booking 302 was a recall then total of 9 for 530041 would have been correct! So close! – jhowe Mar 10 '21 at 18:53
I've rewritten the answer to handle the customer level subtotal as a sum of the parts. Hopefully, it's more intuitive now. – Alexis Olson Mar 10 '21 at 18:57
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/229751/discussion-between-jhowe-and-alexis-olson). – jhowe Mar 10 '21 at 20:42

Mike Honey · Answer 2 · 2021-03-09T21:18:26.620

1

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.

edited Mar 09 '21 at 21:18

answered Mar 07 '21 at 22:32

Mike Honey

14,523
1
24
40

Thanks for your reply, I need something a bit more sophisticated than this, as you can see in my SQL i'm filtering out NULL asset/closed on dates and want to split this count by engineer. I was hoping for something like Recall Rate = SUMMARIZECOLUMNS( WorkOrder[KEY_Customer], WorkOrder[KEY_Asset], Booking[KEY_Engineer], FILTER(WorkOrder, DATESINPERIOD('Date'[Date],LASTDATE(WorkOrder[Closed On]), -7, DAY))) - 1 but keep getting error multiple columns cannot be converted to a scalar value... i've not used summarizecolumns before, can't get the syntax right. – jhowe Mar 08 '21 at 13:32
Its quite flexible, e.g. add Filters for asset/closed on dates (is not blank), add [KEY_Engineer] to the Booking Key. – Mike Honey Mar 08 '21 at 21:05
Ok, but what about the fact that the first occurrence in a 7 day period is not counted as a recall? – jhowe Mar 09 '21 at 11:33
Also this assumes I believe, correct me if i'm wrong that the recalls all have to be within the SAME week. What if they span from one week into the next... – jhowe Mar 09 '21 at 13:15
I edited the measure above to ignore the first occurence. – Mike Honey Mar 09 '21 at 21:19
"Week" is obviously subjective, so I went with the standard definition. Your definition is "prior 7 days"? – Mike Honey Mar 09 '21 at 21:22
Yea so if I went out to fix a machine on a Friday and then three days later got called out to fix the same machine on a Monday that would be a recall and that wouldn't be within the same week. – jhowe Mar 09 '21 at 22:30
That sounds like WEEKNUM, not prior 7 days. So I would leave the Booking Key as described above, possibly adding KEY_Engineer. – Mike Honey Mar 10 '21 at 21:37

DAX Service recall rate measure

2 Answers2