How to get previous value by group (directquery)

[l1nk]

Hello community, i need your help.

I have this table:

I need a chart showing the difference between the current lap and the previous lap for each car. To achieve this my strategy was to add two columns: one with the previous time and another with the difference, something like this:

How can i do this? I'm using DirectQuery and because of this some approaches doesn't work, like for example create an index column...

Thank you in advanced for your help.

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://dropmefiles.com/H8X27
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

[l1nk]

Hi Ahmedx.

Thank you for your help.

When I try to define a measure "difference" the system does not validate the function. The reason is OFFSET, ORDERBY, KEEP and PARTITIONBY 'is not a function'.

I think this is related with the fact that i'm using DirectQuery...

[Ahmedx]

try like this
https://dropmefiles.com/lzZTb

[l1nk]

The system returns the following error message:

"The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows."

At my real scenario the first page of the report is a summary for each car, so theres no partial times. The user can drill through into a car and see all the data related. Is it possible to adjust the measure to use just the small set of data selected?

[ryan_mayu]

@l1nk 

you can try to create two measures

Measure = 
var _last=maxx(FILTER(all('Table'),'Table'[CAR]=max('Table'[CAR])&&'Table'[ID]<max('Table'[ID])),'Table'[ID])
return maxx(FILTER(all('Table'),'Table'[CAR]=max('Table'[CAR])&&'Table'[ID]=_last),'Table'[TIME])+0


Measure 2 = sum('Table'[TIME])-[Measure]

pls see the attachment below

[l1nk]

Hi ryan_mayu.

Thank you for your help.

The system returns the following error message:

"The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows."

At my real scenario the first page of the report is a summary for each car, so theres no partial times. The user can drill through into a car and see all the data related. Is it possible to adjust the measure to use just the small set of data selected?

[ryan_mayu]

I never had so many rows in my powerbi. I don't know how to solve this.

and i seldom use directquery

have you tried to filter the data and create a new table?

you can use FILTER function

i am not sure if this works for directquery.