cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Post Prodigy

## Create new Table from Last record

Hello,

Is there a way to grab only the LAST record for a bunch of records based on the latest date? In SQL server it is super east and extreemly fast. I woudl simply select * from all rows with Max(Even_Date) and that would return me only the rows and all the columns in that row with the latest date.

Doesnt seem possible in DAX.

1 ACCEPTED SOLUTION
Solution Sage

Hello @lcasey

Proud to be a Datanaut!

17 REPLIES 17
Solution Sage

Hello @lcasey

Should be possible with DAX as well. If you post a sample data set along with expected results we can have a look.

Proud to be a Datanaut!

Post Prodigy

Hello,

Below is a screenshot of the underlying data.  I only want to pull those rows with the very latest event date for each company. I highlighted in yellow what the new table would have as rows.

Each yell;ow highlight is the very latest event date for each company.

Solution Sage

Could you post a a dataset I could copy/paste?

gracias

Proud to be a Datanaut!

Post Prodigy

Hello,

https://bsa-my.sharepoint.com/:x:/g/personal/lcasey_bsa_org/EZZwPurDa0JMsi0CSn6PtKwBd8z-qqbYPgrGkGZU...

Solution Sage

@lcasey

try like this:

```FILTER(
NATURALINNERJOIN(
Table_query__1,
SUMMARIZECOLUMNS(
Table_query__1[Company Name - Nombre de Empresa],
"MaxEventDate", MAX( Table_query__1[Event Date - Fecha de Evento] )
)
),
[MaxEventDate] = [Event Date - Fecha de Evento]
)```

Proud to be a Datanaut!

Post Prodigy

Unfortunatly that doesnt work.

Solution Sage

hello  @lcasey

Does your table inside PowerBI have the same name and same column names as the one I am using? I am guessing not.

The Query worked on my side, it looks like it is an issue of field names. I have also noticed that you added an extra FILTER expression at the end of my Query

Proud to be a Datanaut!

Post Prodigy

They should be the same..... I tried copying again but no luck.

Are you positive Dax is even capable of doing this?  In SQL it is litterally so easy and extreemly simple.  Why Dax needs several lines of code to do such a simple thing is really scarry. Doesnt seem like a very well thought out reporting language.

All I need to do is basically just have a seperate table with all the latest rows based on that event date. Every single column should be included so that I can calculate off of those other columns. I cant remove duplicates as I then cant choose to keep the very latest row in the dataset.

Very frustrated with these  Power BI limitations!

Solution Sage

Hello @lcasey

yes it is possible with DAX. I will share the pbix file later today so you can check.

But don't forget that if you feel uncomfortable doing it with DAX you can also create a view in your database and import the view directly

Proud to be a Datanaut!

Post Prodigy

Hello,

This is not a SQL server connection, but a SharePoint connection so I cant create a view of the data.  Power BI imports the entire table.

I really appreciate you taking all this time to help, I thought this would be so simple but it really is very complicated to create a table filtered to only the latest record for each case.

Thanks again.

Solution Sage

@lcasey

by the way, if in your model you are not using the entire table and you only need data for the latest record of each group then it would be better to perform this operation within Power Query and do not overload the model with unecessary data. If that is the case then i can provide a M solution

Proud to be a Datanaut!

Post Prodigy

Hello,

I noticed that there are duplicates in the new table that was created.  Do you know why some records are returned more than one time?

Post Prodigy

I see why the duplicates are there.  On the event Date, sometimes there are multiple records with the same exact date causing the table to load multiple records for a company.

I will need to modify the dates to be unique...:)   Thanks again!

Post Prodigy

Hi,

In certain tabs of the report I am using the entire table, but for a certain tab, I just need the latest record.   There are not too  many records and the tqble will probably never go beyond 10 thousand actual records.

Again, thank you so much for your help on This!

Solution Sage

Hello @lcasey

Proud to be a Datanaut!

Post Prodigy

Thanks!

This is exactly what I was trying to do,  Thank you so much!!!

Post Prodigy

Unfortunatly that doesnt work.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors