Can the Index column "reset" after applying filters?

[sthaya]

Hi Power BI experts,

I am trying to calculate "Miles Travel" by "VEHICLE ID". I have a forumla that works - but only when I am not using any fliters.

Miles Traveled =
 'Fuel Pump'[ODOMETER] - IF(
  'Fuel Pump'[Index] = 0,
  'Fuel Pump'[ODOMETER],
  LOOKUPVALUE(
   'Fuel Pump'[ODOMETER],
   'Fuel Pump'[Index],
   'Fuel Pump'[Index]-1)
 )

Here is a screenshot of my Report without any filters applied. You can see the 'Miles Traveled' works fine.

But my Report will be mostly used to filter by "VEHICLE ID". My formula breaks when I filter by "VEHICLE ID". For example, I only want to see data for VEHICLE ID = 102...

I'm positive the formula breaks because the Index does not "reset" when I apply a filter. Is there a way to reset the Index when I apply a filter? Thank you!

[rafaelmpsantos]

No way...for this you need to create a measure of rank based on date and time...this will be your new index...

[Greg_Deckler]

You can do this leaving your Index alone and using a measure like along the lines of:

Measure 3 = 
VAR __currentIndex = MAX('Fuel Pump'[Index])
VAR __table = ALLSELECTED('Fuel Pump')
VAR __previousIndex = MAXX(FILTER(__table,[Index]<__currentIndex),[Index])
VAR __currentOdometer = MAXX(FILTER(__table,[Index]=__currentIndex),[ODOMETER])
VAR __previousOdometer = MAXX(FILTER(__table,[Index]=__previousIndex),[ODOMETER])
RETURN __currentOdometer - __previousOdometer

[sthaya]

@Greg_Deckler,

I was able to address my Index problem. I combined the DATE and TIME field to make a unique index field. Your formula works for me now. I do have a question. How can I tweak your formula so that the first line will show '0' as the Miles Traveled? I tried several IF/THEN statements but couldn't get it to work.

Miles Traveled = 
VAR __currentIndex = MAX('Fuel Pump'[Index])
VAR __table = ALLSELECTED('Fuel Pump')
VAR __previousIndex = MAXX(FILTER(__table,[Index]<__currentIndex),[Index])
VAR __currentOdometer = MAXX(FILTER(__table,[Index]=__currentIndex),[ODOMETER])
VAR __previousOdometer = MAXX(FILTER(__table,[Index]=__previousIndex),[ODOMETER])
RETURN __currentOdometer - __previousOdometer

Thank you!

[Greg_Deckler · Accepted solution]

@sthaya Cool

Perhaps:

Miles Traveled = 
VAR __currentIndex = MAX('Fuel Pump'[Index])
VAR __table = ALLSELECTED('Fuel Pump')
VAR __min = MINX(__table,[Index])
VAR __previousIndex = MAXX(FILTER(__table,[Index]<__currentIndex),[Index])
VAR __currentOdometer = MAXX(FILTER(__table,[Index]=__currentIndex),[ODOMETER])
VAR __previousOdometer = MAXX(FILTER(__table,[Index]=__previousIndex),[ODOMETER])
RETURN IF(__currentIndex = __min,0,__currentOdometer - __previousOdometer)

[sthaya]

I think I know why the math "shifted". I looked at the Index and some of them are out of order. Your formula calls for the previous index, and that could be anywhere in the data. I did create this Index column in the Query Editor. I think if I can fix the Index order, your formula should work.

[Greg_Deckler]

@sthaya - Can you supply some more extensive sample source data in text or by sharing it via OneDrive or drop box or something? There are a couple of different ways of going about this but apparently I need some more extensive data to test with. If you want, you can send what you have in a private message.

[sthaya]

@Greg_Deckler,

I was thinking about what @rafaelmpsantos was saying in his reply about Indexing by DATE. I used this measure to insert an Index column:

Index = RANKX(ALL('Fuel Pump'),FIRSTNONBLANK('Fuel Pump'[Date],'Fuel Pump'[Date]),,ASC,Dense)

Then I filtered by Vehicle ID "102"

The index appears to be in order this time. I guess it would be very rare that a Vehicle would fill up twice in one day. If that happens, we'll have some rows with the same index number. Would you know how I can create an index by DATE and TIME? That would make the index number unique.

anyways, after I index by DATE, your formula breaks:

I tried to play around with your formula a little, but I couldn't get it to work with this new date Index.

Thank you for your help with this. I think we're almost there.

[rafaelmpsantos]

You may use your index column

Index = RANKX(ALL('Fuel Pump'),FIRSTNONBLANK('Fuel Pump'[Index],'Fuel Pump'[Index]),,ASC,Dense)

will work fine.

[sthaya]

@rafaelmpsantos,

using your measure, the index are still out of order when I filter on Vehicle ID:

When I don't use a filter, the Index starts in order, but after awhile, they are random:

 

I'm trying to see if I can use DATE as the index, like you originally suggested.

[rafaelmpsantos]

You may create a new column concatenate date and hour... in powerquery is the best way to do this 

[sthaya]

Greg,

is this good enough? Let me know if you need more data. Thanks!

ID,TRAN,CARD,MM/DD/YY,HH/MM,T,H,P,USER 1,GALLONS ,VEHICLE ID,ODOMETER,USER 4
00,0480,0116,05/25/18,07:05,1,1,1,XXXXXX,0029.80,XXXX86,116315,XXXXXX
00,0481,0007,05/25/18,08:01,8,1,0,XXXXXX,0000.00,XX1861,XXX100,XXXXXX
00,0482,0007,05/25/18,08:02,1,1,1,XXXXXX,0016.60,XX1861,XXX100,XXXXXX
00,0483,0155,05/25/18,08:12,1,1,1,XXXXXX,0012.30,XXXX96,X99170,XXXXXX
00,0484,0114,05/25/18,08:15,1,1,1,XXXXXX,0004.90,XXX131,XX6554,XXXXXX
00,0485,0110,05/25/18,08:34,1,1,1,XXXXXX,0017.80,XXXX87,106377,XXXXXX
00,0486,0131,05/25/18,08:53,2,0,7,XXXXXX,0000.00,XXXXX1,XXXXXX,XXXXXX
00,0487,????,05/25/18,08:53,7,0,0,XXXXXX,0000.00,XXXXXX,XXXXXX,XXXXXX
00,0488,0131,05/25/18,08:54,1,1,1,XXXXXX,0010.20,XXXX49,151555,XXXXXX
00,0489,0019,05/25/18,09:31,1,1,1,XXXXXX,0020.90,XXXX78,173520,XXXXXX
00,0490,0117,05/25/18,09:36,1,1,1,XXXXXX,0017.20,XXXXX6,X85747,XXXXXX
00,0491,0173,05/25/18,09:40,1,1,1,XXXXXX,0008.30,XXX102,115561,XXXXXX
00,0492,0145,05/25/18,09:49,1,1,1,XXXXXX,0014.20,XXXX83,X90650,XXXXXX
00,0493,0077,05/25/18,11:58,1,1,1,XXXXXX,0026.10,XXXX34,287560,XXXXXX
00,0494,????,05/25/18,12:47,7,0,0,XXXXXX,0000.00,XXXXXX,XXXXXX,XXXXXX
00,0495,????,05/25/18,12:47,7,0,0,XXXXXX,0000.00,XXXXXX,XXXXXX,XXXXXX
00,0496,????,05/25/18,12:47,7,0,0,XXXXXX,0000.00,XXXXXX,XXXXXX,XXXXXX
00,0497,????,05/25/18,12:47,7,0,0,XXXXXX,0000.00,XXXXXX,XXXXXX,XXXXXX
00,0498,0084,05/25/18,12:48,1,1,1,XXXXXX,0031.20,XX5054,106847,XXXXXX
00,0499,0122,05/25/18,14:08,1,1,1,XXXXXX,0021.70,XXXX10,X77530,XXXXXX
00,0500,0100,05/25/18,14:13,1,1,1,XXXXXX,0013.90,XXXXX5,777777,XXXXXX
00,0501,0090,05/25/18,14:16,1,1,1,XXXXXX,0016.70,XXX500,X25000,XXXXXX
00,0502,0098,05/25/18,14:30,1,1,1,XXXXXX,0027.70,XXXXX4,157298,XXXXXX
00,0503,0105,05/25/18,14:42,1,1,1,XXXXXX,0024.00,XXX105,176306,XXXXXX
00,0504,????,05/25/18,14:47,7,0,0,XXXXXX,0000.00,XXXXXX,XXXXXX,XXXXXX
00,0505,0153,05/25/18,14:47,1,1,1,XXXXXX,0015.60,XXX503,XXXX00,XXXXXX
00,0506,0099,05/25/18,15:01,1,1,1,XXXXXX,0015.10,XXXX15,142111,XXXXXX
00,0507,0142,05/25/18,15:15,1,1,1,XXXXXX,0015.00,XXXX32,232797,XXXXXX
00,0508,????,05/25/18,15:20,7,0,0,XXXXXX,0000.00,XXXXXX,XXXXXX,XXXXXX
00,0509,0091,05/25/18,15:21,1,1,1,XXXXXX,0011.00,XXX134,XX1124,XXXXXX
00,0510,????,05/25/18,15:43,7,0,0,XXXXXX,0000.00,XXXXXX,XXXXXX,XXXXXX
00,0511,0126,05/25/18,15:43,1,1,1,XXXXXX,0022.40,XXXX51,131251,XXXXXX
00,0512,0078,05/25/18,16:12,1,1,1,XXXXXX,0016.10,XXXX24,101099,XXXXXX
00,0513,0094,05/25/18,16:36,1,1,1,XXXXXX,0017.70,XXXX18,245262,XXXXXX
00,0514,0104,05/25/18,16:41,1,1,1,XXXXXX,0020.50,XXXX39,417152,XXXXXX
00,0515,0144,05/25/18,16:57,1,1,1,XXXXXX,0019.70,XXXXX2,263697,XXXXXX
00,0516,????,05/26/18,06:18,7,0,0,XXXXXX,0000.00,XXXXXX,XXXXXX,XXXXXX
00,0517,????,05/26/18,06:18,7,0,0,XXXXXX,0000.00,XXXXXX,XXXXXX,XXXXXX
00,0518,0157,05/26/18,06:19,7,0,1,XXXXXX,0000.00,XXXXXX,XXXXXX,XXXXXX
00,0519,????,05/26/18,06:20,7,0,0,XXXXXX,0000.00,XXXXXX,XXXXXX,XXXXXX
00,0520,????,05/26/18,06:24,7,0,0,XXXXXX,0000.00,XXXXXX,XXXXXX,XXXXXX
00,0521,0029,05/26/18,06:25,2,0,2,XXXXXX,0000.00,XXXXXX,XXXXXX,XXXXXX
00,0522,0157,05/26/18,06:25,1,1,1,XXXXXX,0024.50,XXX110,300896,XXXXXX
00,0523,0030,05/26/18,06:31,1,1,1,XXXXXX,0013.30,XXXX30,X61411,XXXXXX
00,0524,0029,05/26/18,06:35,1,1,1,XXXXXX,0001.10,XXX505,XXXXX0,XXXXXX
00,0525,0107,05/26/18,06:59,1,1,1,XXXXXX,0021.50,XXXX29,344025,XXXXXX
00,0526,0116,05/26/18,07:16,1,1,1,XXXXXX,0012.60,XXXX85,141959,XXXXXX
00,0527,0126,05/26/18,07:43,1,1,1,XXXXXX,0012.00,XXXX51,130372,XXXXXX
00,0528,0126,05/26/18,07:46,1,1,1,XXXXXX,0005.80,XXX509,XXXX00,XXXXXX
00,0529,0113,05/26/18,08:32,1,1,1,XXXXXX,0012.60,XXXX17,104351,XXXXXX
00,0530,0173,05/26/18,08:36,1,1,1,XXXXXX,0005.50,XXX102,115730,XXXXXX
00,0531,0154,05/26/18,08:42,1,1,1,XXXXXX,0019.90,XXXX26,122312,XXXXXX
00,0532,0011,05/26/18,08:48,1,1,1,XXXXXX,0026.30,XXXX82,144499,XXXXXX
00,0533,0019,05/26/18,09:18,1,1,1,XXXXXX,0019.20,XXXX53,117099,XXXXXX
00,0534,????,05/26/18,09:22,7,0,0,XXXXXX,0000.00,XXXXXX,XXXXXX,XXXXXX
00,0535,????,05/26/18,09:23,7,0,0,XXXXXX,0000.00,XXXXXX,XXXXXX,XXXXXX
00,0536,0001,05/26/18,09:23,7,0,4,XXXXX0,0000.00,XXXXXX,XXXXXX,XXXXXX

[sthaya]

Greg,

Thank you! The formula started out correct, but then the math/answer "shifted" somehow. You can see the results here..

I'm sure it just needs a little tweaking. I'll keep working on it. But do you know what would cause the "shift" like that? The "-3106" is concerning as I do not know where that would have come from - even with the "shift".