Forum Discussion
Creating a table with Only the latest rows
Does anyone Know how to create a table from JUST the latest rows?
For example,
In the picture below
# 1 There are 8 records with CaseIDid 12
#2 Out of those 12 records, only the very latest row with the Max Event_date 12/03/2018 should be returned
How can Power BI simply create a new table and filter out every single row Except for the very latest row with the highest Event_date?
13 Replies
Hello,
Does anyone know how to create a table from another table by simply using the latest record in the original table?
Hello, you could do this in two ways:
1. in power query filter the date to the max date using groupby and max on the date column
2. using DAX make a new table (modelling ribbon): NEW TABLE = CALCULATETABLE('Date', LASTDATE('Date'[Calendar_Date]))
// If this is a solution please mark as such
That did not work,
It only returned 1 record out of hundreds.
The new table needs to iterate through every single row , which there are 800 rows , then out of those 800 rows , only return the rows with the latest event date for each case.
I have posted several times on this and it seems Power BI cant do it. Maybee that why people are using SQL for any type of real reporting as something this simple just seems to not be possible
Let me know if you can think of anything.
Thanks
So is it a case of wanting to return not the row where the date is the maximum of all rows but the rows where the date is the maximum for that particular caseid? For eample you might have caseid1 with a maximum date of 1/1/2018 and caseid with a maximum date of 31/10/2018 and you wish to show both?
If thats the case do it in power query. group by caseid and return the max date in the aggregation. something like 5 or 6 clicks to do it
// if this is a solution please mark as such
