Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
*** 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
Solved! Go to 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.
I can attach the file here or send to you through private message, your choice.
Thanks
Proud to be a Super User!
Hi @jhowe1 ,
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?
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
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
Hope that helps
Proud to be a Super User!
Hi @richbenmintz I think we're getting close however i'm not getting any results from this measure
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,
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?
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
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
)
)
I hope this helps you a little more,
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
)
)
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!
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
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?
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.
I can attach the file here or send to you through private message, your choice.
Thanks
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.
Proud to be a Super User!
Hi Rich,
Not sure about these two counts for new recall rate in your screenshot... why is this?
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |