Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to 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!
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.
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:
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.
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.
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
91 | |
87 | |
84 | |
68 | |
49 |
User | Count |
---|---|
131 | |
110 | |
96 | |
70 | |
67 |