Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Accessing Previous Rows in Filtered Context

The data I'm trying utilize exists in the following context:

 

Different equipment is on site for various quantities for varying amounts of time, as determined by dates on site.

 

I need to establish the calculated days on site as follows:

For the Power Console:

  Qty On Site:   10          8          5

  Days On Site:  3        3+2        3+2+1

 

I established the qty index to rank the quanities thinking I could then use this to grab the previous days value (2) for the lower ranked 8 qty and add it to the value of  3 days to get a total of five days on site.  Unfortunately the previous index grabbed appears to be one that exists in the raw data outside the filter context and is associated with another random row in the raw data.

 

Is there a way to get a previous value for the days column that takes into account the filtered context of the matrix?

 

A subset of the data:

formDataGuidrowGuidequipment_descriptionequipment_qtyEquipment DaysQty IndexDate_
0f17632e-aa44-4c7b-9f0a-0452fb094251d6e0bdc9-c3cb-4337-979b-0f0f4433f7d9Truck83808-Aug-21
0f17632e-aa44-4c7b-9f0a-0452fb09425184bf9557-3049-4faf-bdeb-ef1f4c24bcb6Chainfall25408-Aug-21
0f17632e-aa44-4c7b-9f0a-0452fb0942515590c92a-93a3-4d77-9845-9570ee6ca0f8Power Console103908-Aug-21
20d8daf7-b9f1-42e4-b888-8f3d2a48540bbf8ab457-5b92-4c7b-9db2-c3834bc6746eTruck83811-Aug-21
20d8daf7-b9f1-42e4-b888-8f3d2a48540b876388ee-9f3d-4e23-80e2-6d77b3429537Chainfall25411-Aug-21
20d8daf7-b9f1-42e4-b888-8f3d2a48540b1bf5d3f7-6211-4b8a-a900-ae64910665c7Power Console103911-Aug-21
32f0fc9f-214a-45cb-b739-9ca61c7cde612fc80227-ad2b-4bf1-a243-2cab25e0786dTruck21407-Aug-21
32f0fc9f-214a-45cb-b739-9ca61c7cde61205644b2-6ed6-40b0-bb5b-a79cf41d866aChainfall41507-Aug-21
32f0fc9f-214a-45cb-b739-9ca61c7cde610ace0683-e690-4772-b3cc-4bdddc2f9bcbPower Console51607-Aug-21
3aea03f7-9afb-46e3-b3d9-c0654aa32fb4f5118d85-8abf-41e4-b04f-5306a4d6424bTruck52612-Aug-21
3aea03f7-9afb-46e3-b3d9-c0654aa32fb4e7fb97cb-db76-4c14-bdf2-8d8307226da0Power Console82812-Aug-21
3aea03f7-9afb-46e3-b3d9-c0654aa32fb49be3ba09-1c82-404a-ac2b-baa0a3f4f51fChainfall25412-Aug-21
98bc23fb-a78d-4bbc-8c70-2b0cedabb8bb9aa994f5-e87b-4793-8ba6-8aaf5f0e3696Power Console103910-Aug-21
98bc23fb-a78d-4bbc-8c70-2b0cedabb8bb69294bdb-64ac-4cbe-97cf-27039c8fe9eaChainfall25410-Aug-21
98bc23fb-a78d-4bbc-8c70-2b0cedabb8bb68d0ec31-e361-40b9-bb9a-2327ac141069Truck83810-Aug-21

 

 

The qty index column is calculated as 

Qty Index = rankx(form_itemized_field_ticket_equipment_row, form_itemized_field_ticket_equipment_row[equipment_qty],,1,Dense)
 
The Equipment Days Test is a Measure:
Equipment Days Test = VAR _CurrentRowIndex = max(form_itemized_field_ticket_equipment_row[Qty Index]) VAR _Equipment = max(form_itemized_field_ticket_equipment_row[Equipment No and Description]) VAR _Job = FIRSTNONBLANK(form_itemized_field_ticket[job_no], form_itemized_field_ticket[job_no]) VAR _qty = max(form_itemized_field_ticket_equipment_row[equipment_qty]) VAR _PreviousRowIndex = Calculate(MAX(form_itemized_field_ticket_equipment_row[Qty Index]), FILTER(form_itemized_field_ticket_equipment_row, form_itemized_field_ticket_equipment_row[Equipment No and Description] = _Equipment && related(form_itemized_field_ticket[job_no]) = _Job))  VAR _Result = Calculate(DISTINCTCOUNT(form_itemized_field_ticket[date_]), filter(form_itemized_field_ticket_equipment_row, form_itemized_field_ticket_equipment_row[equipment_qty] <> BLANK() && form_itemized_field_ticket_equipment_row[Equipment No and Description] = _Equipment && related(form_itemized_field_ticket[job_no]) = _Job && form_itemized_field_ticket_equipment_row[equipment_qty] = _qty)) RETURN _Result
 
I've attempted to add a _previousresult VAR to the above from which to calculate _Result + _previousresult to return without any success in writing a formulation that works.
 
One of my many attempts below to put the Measure that is Equipment Days Test into a column format that would make the calculation easier:
Instead of days on site of  1, 2, 3  this gives 1, 4, 9 however.
 
Equipment Days = VAR _CurrentRowIndex = form_itemized_field_ticket_equipment_row[Qty Index] VAR _Equipment = form_itemized_field_ticket_equipment_row[Equipment No and Description] VAR _Job = related(form_itemized_field_ticket[job_no]) VAR _qty = form_itemized_field_ticket_equipment_row[equipment_qty] VAR _PreviousRowIndex = Calculate(MAX(form_itemized_field_ticket_equipment_row[Qty Index]), FILTER(form_itemized_field_ticket_equipment_row, form_itemized_field_ticket_equipment_row[Equipment No and Description] = _Equipment && related(form_itemized_field_ticket[job_no]) = _Job && form_itemized_field_ticket_equipment_row[Qty Index] < _CurrentRowIndex )) VAR _PreviousResult = Calculate( MIN(form_itemized_field_ticket_equipment_row[equipment_qty]), Filter(form_itemized_field_ticket_equipment_row, form_itemized_field_ticket_equipment_row[Qty Index] = _PreviousRowIndex)) VAR _Result = Calculate(DISTINCTCOUNT(form_itemized_field_ticket[date_]), filter(form_itemized_field_ticket_equipment_row, form_itemized_field_ticket_equipment_row[equipment_qty] <> BLANK() && form_itemized_field_ticket_equipment_row[Equipment No and Description] = _Equipment && related(form_itemized_field_ticket[job_no]) = _Job && form_itemized_field_ticket_equipment_row[equipment_qty] = _qty)) RETURN _Result
 
Any pointers or tips would be appricaited at this point.  I've attempted to create secondary tables, convert to columns, utilize measures and I'm stuck.

 

Need Help

1 Reply

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    Anonymous Should be posssible. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
    The basic pattern is:
    Measure = 
      VAR __Current = MAX([Value])
      VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

      VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
    RETURN
      __Current - __Previous

     

    You could use a measure like this in an ADDCOLUMNS statement within another measure to achieve what you want.