Look up previous transaction + Very slow

[11097486]

Hello, I want to be able to lookup for the previous transaction date in a table, but I have 6 million rows and 64 000 different RFID Codes. The 2 methods I tried described below do not work in this situation because there way to many rows. Is there any solution to improve loading time?

Here's my table:

Method 1 in DAX: Use Calculate with earlier function

Method 2 in Power Query: Duplicate the table. Create an index for both tables (Table 1 starting at 0, Table 2 at 1), Merging table 2 to table 1 with the Index to get previous date.

As stated, with 6 million rows none of these methods work.

Thank you

[parry2k · Accepted solution]

@11097486 reason I asked about the data source, I would recommend to have this join/logic in the backend on SQL server rather than doing it in PQ. PQ is not great when it comes to grouping and merging/sorting etc. There are many technical blog posts around that but not going into detail.

You have two options:

- take this to the backend and do the data prep there (preferred method)

- or use Power BI dataflows, it comparatively much faster and you can schedule it to do the job, and then you can use Power BI dataflows in Power BI to visualize the data.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@11097486 what is your data source? Why do you want to add this as a column? What is the final requirement?

[11097486]

It's an SQL server database

I want to compare the delay between transactions for RFID Codes

[MFelix]

Hi  @11097486 ,

Using a duplicat of the table does not seem to be the best option, you can use the index option within the same table and then get the previous value number check the video below on how to do it.

[11097486]

Hello, her solution unfortunately doesn't work. I have 86 000 rows in a table. I do exactly what she says and when I upload I have more than 200 000 rows (it never finished uploading so I stopped). It seems I'm not the only one experiencing this. Thank you

[parry2k]

@11097486 what are you trying to achieve? Do you want to add this as a column or measure?

[11097486]

Hello, add this to a column.