We have devices that go through the support process (called RMA) when they encounter any issue.
Following is the sample data for a device..
- Each device can have multiple RMA tickets..
- Each Rma can have one or many revisions. Any change in a record can trigger new revision.
- Rma can go through different states 1 (Active), 2 (Vendor Investigate), 3 (Vendor Returned), 6 (Closed).
- When a vendor chose StateId (3), he is supposed to mark a resolution either 1 (Not set), 2 (Resolved), 3 (No Issue found)
- Startdate is the updatetime of each Rma Revision.
- Enddate is the updatetime of the next revision. For the latest revision the updatetime is blank.
Now I looking for the Devices that are refailed within the next 7 days after the RMA is fixed (StateId - 6) and Closed ( ResolutionId -2). This measure should working for any reporting period (Day, week or month).
For e.g; if a user selects the reporting period - 06/20 till 06/27, I look for all the devices with RMA's that are closed and fixed and for each of those I will see if there are any RMA's that refailed in the next 7 days. Once an RMA is closed, it can always open with an Active (StateId -1) state.
(or) the user can select a reporting period of a day 06/20.. The dax logic should check all the devices with RMA's closed and fixed and if there are refails for the same device with in the next 7 days after the closed date..
In the below case, My refail count will be 2. I will first identify the revision 3's minimum update time when it is closed and fixed and count the # of occurences of RMA's with active state.
Devicename, RmaId, Revision, StateId, ResolutionId, Startdate, enddate
dev1, rma1, 1, 1, blank, 06/20/2018, 06/21/2018
dev1, rma1, 2, 2, blank, 06/21/2018, 06/22/2018
dev1, rma1, 3, 6, 2, 06/22/2018, blank
dev1, rma2, 1, 1, blank, 06/23/2018, 06/24/2018
dev1, rma2, 2, 6, 2, 06/24/2018, blank
dev1, rma3, 1, 1, blank, 06/25/2018, blank
Below is the query that I started working with, however it's not identifying the correct refail count. Please advice -
DEFINE MEASURE 'Rma Revisions'[reactivation count] =
COUNTROWS (
CALCULATETABLE (
FILTER (
SUMMARIZE (
FILTER ( 'Rma Revisions', [stateid] = 6 && [resolutionid] = 2 && 'Rma Revisions'[DeviceName] = "01f9a585-482b-4941-b42a-4529e9f2265d" ),
'Rma Revisions'[DeviceName],
'Rma Revisions'[StartDate]
),
NOT ISEMPTY (
FILTER (
CALCULATETABLE (
'Rma Revisions',
'Rma Revisions'[Stateid] = 1,
( 'Rma Revisions'[FaultCode] >= 60000
&& 'Rma Revisions'[FaultCode] <= 60099 )
|| ( 'Rma Revisions'[FaultCode] >= 60200
&& 'Rma Revisions'[FaultCode] <= 69999 ),
ALL ( 'Calendar' ),
ALL ( 'Rma Revisions'[StartDate] )
),
EARLIER ( 'Rma Revisions'[StartDate] ) < [StartDate]
&& [StartDate]
< EARLIER ( 'Rma Revisions'[StartDate] ) + 7
)
)
)))
EVALUATE
SUMMARIZECOLUMNS(
"y", CALCULATE (
'Rma Revisions'[reactivation count],
DATESBETWEEN ( 'Calendar'[Date], "6/20/2018", "6/27/2018" )
)
