Forum Discussion

l1nk's avatar
l1nk
Frequent Visitor
3 years ago

How to get previous value by group (directquery)

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.

Need Help

7 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      l1nk
      Frequent Visitor
      3 years ago

      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's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        3 years ago

        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.

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      l1nk
      Frequent Visitor
      3 years ago

      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...