Create new Table from Last record

[lcasey]

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.

[LivioLanzo]

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.

[lcasey]

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.

[LivioLanzo]

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

gracias

[lcasey]

Hello,

Here is a link to the actual spreadsheet:

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

[LivioLanzo]

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

[lcasey]

Unfortunatly that doesnt work.

 

[LivioLanzo]

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

[lcasey]

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!

[LivioLanzo]

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

[lcasey]

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.

[LivioLanzo]

@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

[lcasey]

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?

[lcasey]

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!

[lcasey]

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!

[LivioLanzo · Accepted solution]

Hello @lcasey

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

[lcasey]

Thanks!

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

[lcasey]

Unfortunatly that doesnt work.