Forum Discussion

jhowe1's avatar
jhowe1
Icon for Helper III rankHelper III
5 years ago
Solved

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 strug...
Need Help
  • richbenmintz's avatar
    richbenmintz
    5 years ago

    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

v-lionel-msft's avatar
v-lionel-msft
Icon for Community Support rankCommunity Support
5 years ago

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.

 

 

jhowe1's avatar
jhowe1
Icon for Helper III rankHelper III
5 years ago

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