Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
lcasey
Post Prodigy
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.png

1 ACCEPTED SOLUTION

Hello @lcasey

 

check This: https://1drv.ms/u/s!AiiWkkwHZChHj0P8SqorW4aZ4eGA

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

17 REPLIES 17
LivioLanzo
Solution Sage
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.

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

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.

 

 

1.png

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

 

gracias

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Hello,

 

Here is a link to the actual spreadsheet:

 

 

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

 

 

 

 

 

 

 

@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]
)

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Unfortunatly that doesnt work.

 

 

 2.png

 

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

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!

 

 

 

 

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

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.

@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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

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?

 

 

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!

 

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!

Hello @lcasey

 

check This: https://1drv.ms/u/s!AiiWkkwHZChHj0P8SqorW4aZ4eGA

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Thanks!

 

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

 

 

Unfortunatly that doesnt work.

 

 

 

1.png

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.