Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jhowe1
Helper III
Helper III

Service recall rate measure

*** reposting as seems to have been blocked when i added word measure to the title/subject in original post, added totals to show expected results for count of the group ***

 

Hi all,

 

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

Screenshot 2021-03-05 at 10.29.18.png

1 ACCEPTED SOLUTION

Hi @jhowe1 ,

 

I think a lot of the problem stems from the way the data is related, to me the booking is the fact, given the relationships, so I added the asset and closed on keys to the booking table, then created a calc column that identifies if the booking is a recall

Recall Rate Flag = 
VAR _closed_on = Booking[Closed_on]
VAR _closed_minus_7 = DATE(YEAR(Booking[Closed_on]), MONTH ( Booking[Closed_on] ), DAY (Booking[Closed_on])) - 7
VAR _customer =  ( Booking[KEY_Customer] )
VAR _asset =  ( Booking[KEY_Asset] )
VAR _engineer =  ( Booking[KEY_Engineer] )
VAR _booking =  ( Booking[KEY_WorkOrder] )
RETURN
    if(CALCULATE (
        COUNTROWS ( Booking ),
        FILTER(
            ALL( Booking ),
              _asset <> -1
              && Booking[Closed_On] < _closed_on
                && Booking[Closed_On] >= _closed_minus_7 
                && Booking[KEY_Asset] = _asset
                && Booking[KEY_Customer] = _customer
                && Booking[KEY_Engineer] = _engineer
        )
    )>0, 1)

Then created a measure that counts the bookings that are recalls

Recall Rate New = sum('Booking'[Recall Rate Flag])

 which results in the image below, you may need to scrub the data a little on the way in to ensure that you have a one to one on the workorder and booking.

richbenmintz_0-1615484865046.png

I can attach the file here or send to you through private message, your choice.

Thanks



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


View solution in original post

22 REPLIES 22
v-lionel-msft
Community Support
Community Support

Hi @jhowe1 ,

 

v-lionel-msft_0-1615172521392.png

First, you obtain the above table through a SQL query, and then calculate the ‘recall rate’ from the above table, but I have some doubts about the calculation logic of the ‘recall rate’.
1. What does the null value of each group in ‘recall rate’ mean?
2. Is the ‘recall rate’ group accumulation?
Please give an example to illustrate the calculation logic of ‘recall rate’.

Or is this what you want?

v-lionel-msft_1-1615173140702.png

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Hi thanks for your reply it is much appreciated. As I stated clearly in the post I wish to count customer, asset combinations within a 7 day period. A recall is counted AFTER the first occurrence, hence the blank in the first row in the groups.  I believe I need something like this however i've not used SUMMARIZECOLUMNS before and can't get the syntax correct, i keep getting errors 'the expression refers to multiple columns. multiple columns cannot be converted to a scalar value. I will be wanting to display the count of customer, asset combinations within 7 day period, PER Engineer in a visual. (Engineer is not important in the calculation i just want to be able to split by engineer in a visual.)

 

 

 

 

Recall Rate = 
SUMMARIZECOLUMNS(
    WorkOrder[KEY_Customer],
    WorkOrder[KEY_Asset],
    Booking[KEY_Engineer],
    FILTER(WorkOrder, DATESINPERIOD('Date'[Date],LASTDATE(WorkOrder[Closed On]), -7, DAY))) - 1

 

 

 

 

jhowe1
Helper III
Helper III

Can I get some assistance with this please, it will start to become urgent thanks. 

Hi @jhowe1,

 

Please have a look at the following Measure, very similar to the answer from @v-lionel-msft but accounts for the 7 day window, please note that the measure will get evaluated in context of the data being displayed in the visual and the filters applied and will only count the recalls in the window represented by the contect. and the prior 7 days, so if an item is fixed once every 8 days it will not be counted as a recall. 

Hope that makes sense

Recall Rate = 
var _closed_on = SELECTEDVALUE('Table'[ClosedOn])
var _closed_minus_7 = DATE(YEAR(SELECTEDVALUE('Table'[ClosedOn])), MONTH(SELECTEDVALUE('Table'[ClosedOn])), DAY(SELECTEDVALUE('Table'[ClosedOn]))) - 7
var _customer = SELECTEDVALUE('Table'[Customer])
var _asset = SELECTEDVALUE('Table'[Asset])
return
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        ALL('Table'),
        ('Table'[ClosedOn]<_closed_on && 'Table'[ClosedOn] >=_closed_minus_7)  &&
        'Table'[Asset] = _asset &&
        'Table'[Customer] = _customer
       )
    )

results in the following table, in order to display the null you will need to tell the visual to display blank values

richbenmintz_0-1615209258167.png

 

Hope that helps

 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Hi @richbenmintz I think we're getting close however i'm not getting any results from this measure 

jhowe1_0-1615212695250.png

jhowe1_2-1615213082423.png

 

 

What am I missing? Also i want this count to be able to be split by engineer (see sql code) which is in a different table 'dbo.FactBooking'

Hi @jhowe1 

 

Are you able to provide a sample pbix file, would make things much easier for me and other members of the community to provide the correct answer.

Thanks,



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Hi Rich,

 

Just realised you're using selected measure and I only had engineer name and recall rate selected. I replicated your table visual (results incorrect anyway, there's dates in here that aren't even in my factworkorder table). Some of the data in this dataset maybe sensitive, I would prefer a quick teams call if possible, if the pasted is not enough?

 

Screenshot 2021-03-08 at 15.05.20.png

 

and pasted my DAX below

 

 

Recall Rate = 
var _closed_on = SELECTEDVALUE(WorkOrder[Closed On])
var _closed_minus_7 = DATE(YEAR(SELECTEDVALUE(WorkOrder[Closed On])), MONTH(SELECTEDVALUE(WorkOrder[Closed On])), DAY(SELECTEDVALUE(WorkOrder[Closed On]))) - 7
var _customer = SELECTEDVALUE(WorkOrder[KEY_Customer])
var _asset = SELECTEDVALUE(WorkOrder[KEY_Asset])
return
CALCULATE(
    COUNTROWS(WorkOrder)
,FILTER(
      ALL(WorkOrder),
        (WorkOrder[Closed On] <_closed_on && WorkOrder[Closed On] >=_closed_minus_7)  &&
       WorkOrder[KEY_Asset] = _asset &&
        WorkOrder[KEY_Customer] = _customer
       )
    )

 

 

 SQL 

 

 

SELECT
    FB.KEY_Engineer,
    FWO.KEY_Customer,
    FWO.KEY_Asset,
    FWO.ClosedOn
FROM pbi.FactWorkOrder AS FWO 
JOIN pbi.FactBooking AS FB ON FB.KEY_WorkOrder = FWO.KEY_WorkOrder
WHERE FWO.WorkOrderType = 'Breakdown'
    AND KEY_Asset IS NOT NULL
    AND ClosedOn IS NOT NULL
ORDER BY ClosedOn DESC

 

 

Data

 
KEY_Engineer KEY_Customer KEY_Asset ClosedOn
613 540527 -1 2021-02-10 16:59:23
797 540527 248 2021-02-10 16:24:35
797 540527 -1 2021-02-10 16:24:17
797 540527 23537 2021-02-10 16:24:01
797 540527 248 2021-02-10 16:23:14
800 540527 248 2021-02-10 16:02:48
797 540527 248 2021-02-10 11:55:16
477 540527 248 2021-02-10 11:13:07
428 530041 2053 2021-01-29 09:29:41
428 530041 6676 2021-01-28 10:29:05
797 530041 6676 2021-01-26 11:06:03
797 530041 6676 2021-01-26 10:52:17
797 530041 6676 2021-01-25 17:54:00
797 540527 5256 2021-01-25 16:41:19
797 540527 5256 2021-01-25 15:15:50
428 533252 5592 2021-01-25 14:45:46
797 540527 14966 2021-01-25 14:38:41
428 540527 5256 2021-01-25 12:21:53
797 540527 14966 2021-01-20 16:11:23
797 530041 19987 2021-01-18 11:36:46
797 530041 19987 2021-01-15 12:07:40
428 530041 24564 2021-01-14 10:40:38
428 530041 19987 2021-01-13 12:13:32
428 540527 5256 2021-01-13 11:56:52
428 530041 19987 2021-01-13 11:38:52
428 530041 19987 2021-01-13 11:10:11
797 530041 19987 2021-01-13 10:21:15
797 540527 5256 2021-01-12 14:43:50
428 540527 5256 2021-01-12 13:49:04
797 530041 19987 2021-01-11 12:30:45
428 530041 19987 2021-01-11 11:13:42
797 530041 19987 2021-01-11 11:12:56
797 530041 19987 2021-01-08 16:48:18
797 530041 19987 2021-01-08 16:41:46
795 541084 12826 2021-01-08 08:21:34
795 533979 5135 2021-01-06 14:59:43
795 533252 10821 2021-01-06 11:49:06
795 540527 5256 2021-01-06 11:34:45
795 530041 24564 2021-01-06 11:19:50
428 530041 19987 2021-01-04 11:40:28
428 530041 19987 2021-01-04 11:26:13
428 530041 19987 2020-12-17 10:12:26
428 530041 19987 2020-12-17 10:12:26
428 530041 19987 2020-12-16 12:07:14
428 530041 19987 2020-12-15 16:39:01
428 530041 19987 2020-12-15 15:50:49
428 530041 19987 2020-12-15 13:07:31
797 530041 19987 2020-12-10 15:34:48
428 530041 19987 2020-12-04 10:29:21
800 530041 19987 2020-11-30 16:30:48
800 530041 19987 2020-11-30 16:30:48
800 530041 -1 2020-11-30 16:16:02
800 530041 -1 2020-11-30 16:16:02
795 530041 19987 2020-11-24 16:17:20
795 530041 19987 2020-11-24 14:42:19
795 530041 19987 2020-11-16 15:48:30
795 529402 14956 2020-11-16 15:30:34
795 529402 14956 2020-11-16 15:30:34
795 529402 14956 2020-11-16 15:30:34
795 530041 19987 2020-11-12 10:36:12
795 530041 19987 2020-11-12 10:36:12
797 530041 19987 2020-11-11 17:07:12
 
The extra dates in the table visual are coming from work orders that don't have a booking, where as i'm only interested in work orders that have a booking for this calculation. 
 
jhowe1_0-1615219759282.png

 

Hi @jhowe1 ,

With the data provided I have added a new filter condition to the measure, which again I believe provides the desired result, counts the assets serviced and completed more than once in a 7 day period, by customer and engineer.

 

Recall Rate 2 = 
var _closed_on = SELECTEDVALUE(WorkOrder[Closed On])
var _closed_minus_7 = DATE(YEAR(SELECTEDVALUE(WorkOrder[Closed On])), MONTH(SELECTEDVALUE(WorkOrder[Closed On])), DAY(SELECTEDVALUE(WorkOrder[Closed On]))) - 7
var _customer = SELECTEDVALUE(WorkOrder[KEY_Customer])
var _asset = SELECTEDVALUE(WorkOrder[KEY_Asset])
var _engineer = SELECTEDVALUE('WorkOrder'[KEY_Engineer])
return
CALCULATE(
    COUNTROWS(WorkOrder)
,FILTER(
      ALL(WorkOrder),
        (WorkOrder[Closed On] <_closed_on && WorkOrder[Closed On] >=_closed_minus_7)  &&
       WorkOrder[KEY_Asset] = _asset &&
        WorkOrder[KEY_Customer] = _customer &&
        WorkOrder[KEY_Engineer] = _engineer
       )
    )

richbenmintz_0-1615251506355.png

 

I hope this helps you a little more,



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Hi @richbenmintz , thanks again for your time, very close now. As you can see in my SQL, engineer comes from the booking table, not work order, therefore I am unable to filter a workorder column by engineer. I also had to filter out workorders that do not have bookings and null assets (-1) as you can see from my DAX. Ideally what i'm after is a number (recall rate) which can be split by (i.e. a total number broken down) engineer name in a visual (associated with key_engineer, from engineer dim), or customer name (customer dim) or asset name (asset dim). The key's etc. won't make sense to the user, it was just to get the data correct. How would we refactor the DAX to support this? 

 

 

 

Recall Rate = 
VAR _closed_on = SELECTEDVALUE ( WorkOrder[Closed On] )
VAR _closed_minus_7 = DATE ( YEAR ( SELECTEDVALUE ( WorkOrder[Closed On] ) ), MONTH ( SELECTEDVALUE ( WorkOrder[Closed On] ) ), DAY ( SELECTEDVALUE ( WorkOrder[Closed On] ) ) ) - 7
VAR _customer = SELECTEDVALUE ( WorkOrder[KEY_Customer] )
VAR _asset = SELECTEDVALUE ( WorkOrder[KEY_Asset] )
VAR _engineer = SELECTEDVALUE ( Booking[KEY_Engineer] )
VAR _booking = SELECTEDVALUE ( Booking[KEY_WorkOrder] )
RETURN
    CALCULATE (
        COUNTROWS ( WorkOrder ),
        FILTER(
            ALL( WorkOrder ),
             ( NOT( ISBLANK ( _booking ) ) && _asset <> -1 && WorkOrder[Closed On] < _closed_on
                && WorkOrder[Closed On] >= _closed_minus_7 )
                && WorkOrder[KEY_Asset] = _asset
                && WorkOrder[KEY_Customer] = _customer
        )
    )

 

Screenshot 2021-03-09 at 11.01.00.png

 

Hi @jhowe1 ,

I really need a copy of a pbix file with representation of your data and data model, otherwise i think I will just be throwing spagetthi at the wall!



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Ok I understand. How can I upload this? I will need to go through and remove any sensitive information from the model... or happy to do a quick call might be faster.

 

@richbenmintz I have created a basic masked version of my dataset, please let me know how I can get this to you asap. Thanks for all the help.

Hi @jhowe1 

please share through onedrive or dropbox or any file share tool you are comfortable with



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


@richbenmintz I have sent link privately, so for green group (in first screenshot) i would expect to see recall for engineer 797, 428, 4 each. Orange group I would expect to see count of 2 for 797 and 1 for 428. in a stacked bar chart for example with simply engineer name/recall. It would be nice to show count of 8 (green group) for customer/asset level in another visual, but primarily concerned with getting the engineers showing correct totals.

Hi @jhowe1 ,

I will try to have a deeper look today, what I do see however is that you have a one to many relationship between the booking and the work order, and the engineer lives on the booking, what happens if multiple engineers work on the asset, the closed on is on the one side of the relationship, who closed it?



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Great thanks rich, so a work order is created, then a booking is scheduled against the work order, I believe there should only be one engineer against a booking, they create another work order if they need to send another engineer. I know it is one to many but in practise they've told me there should only be one booking against a work order. Give me a shout if you have any other questions. Forgot to add this is test data they're working with, they haven't gone live with their system yet.

Hi @jhowe1 ,

 

I think a lot of the problem stems from the way the data is related, to me the booking is the fact, given the relationships, so I added the asset and closed on keys to the booking table, then created a calc column that identifies if the booking is a recall

Recall Rate Flag = 
VAR _closed_on = Booking[Closed_on]
VAR _closed_minus_7 = DATE(YEAR(Booking[Closed_on]), MONTH ( Booking[Closed_on] ), DAY (Booking[Closed_on])) - 7
VAR _customer =  ( Booking[KEY_Customer] )
VAR _asset =  ( Booking[KEY_Asset] )
VAR _engineer =  ( Booking[KEY_Engineer] )
VAR _booking =  ( Booking[KEY_WorkOrder] )
RETURN
    if(CALCULATE (
        COUNTROWS ( Booking ),
        FILTER(
            ALL( Booking ),
              _asset <> -1
              && Booking[Closed_On] < _closed_on
                && Booking[Closed_On] >= _closed_minus_7 
                && Booking[KEY_Asset] = _asset
                && Booking[KEY_Customer] = _customer
                && Booking[KEY_Engineer] = _engineer
        )
    )>0, 1)

Then created a measure that counts the bookings that are recalls

Recall Rate New = sum('Booking'[Recall Rate Flag])

 which results in the image below, you may need to scrub the data a little on the way in to ensure that you have a one to one on the workorder and booking.

richbenmintz_0-1615484865046.png

I can attach the file here or send to you through private message, your choice.

Thanks



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Thanks again for your help it's much appreciated. 

Sorry just got your msg. The workorder booking relationship has always been a problem for me normally I would never have a direct relationship between two fact tables. The process is a WO is created, then a booking is scheduled with an engineer assigned. I've been using the WO as the master table as it's where the process begins, however the booking is a lower grain (transactional level). I think i've been approaching the model the wrong way, the lowest level should be the master table. I think i'm going to try making the direct wo/booking relationship inactive and use the booking as the core table and lookup the higher level values i need either through sql/dax.  Annoyingly in all the books etc. there's never any field service examples which i actually find to be more complicated than most. You can send me link to the model in a private msg, how would you model this process correctly? Thanks again for the help.

Hi @jhowe1 ,

 

I would bring all the keys you need into the table where you are deriving the recall metric, then you will be able to get the values you need and perform all of  your context manipulation in one spot. Have a look at the file i sent you as that is what i attempted to do with your sample data. 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Hi Rich, 

 

Not sure about these two counts for new recall rate in your screenshot... why is this? Screenshot 2021-03-17 at 16.56.20.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.