Forum Discussion
DAX or M Query
Hello All,
So there is this table I am importing along with 30 others for one of my reports.
This table consists of ID, Description Insert Date and Created By.
What I am trying to achieve is for this table to look through every occurrence of the 'ID' value and ONLY filter the Latest/MAX value based on its 'Date'. So based on the below table for example for 'ID' number 12 there are 3 entries and in the end I want it to filter to the row of 'Insert Date' --> 5/15/2018 18:25.
ID Description Insert Date Created By
| 12 | test 123111 | 5/10/2018 13:42 | user 1 |
| 7 | 121 | 5/11/2018 13:42 | user 1 |
| 12 | 121 | 5/12/2018 14:12 | user 2 |
| 7 | 1 | 5/12/2018 14:13 | user 3 |
| 7 | test | 5/14/2018 14:52 | user 4 |
| 12 | this is the latest | 5/15/2018 18:25 | user 2 |
| 7878 | Test comment | 5/15/2018 18:58 | user 1 |
| 787 | test 1 | 5/16/2018 19:00 | user 2 |
| 7878 | test 1 | 5/15/2018 20:42 | user 2 |
| 989 | test 1 | 5/15/2018 21:02 | user 2 |
| 434 | test comment 2 | 5/18/2018 22:00 | user 2 |
I have tried options like:
- Indexing
- Trying to play with List.Max, List.Last
- Attempting to add custom column containing values which indicate which row is the latest one
- CaculcateTable(NaturalLeftOuterJoin) - Trying to use a filter for MAX(InsertDate) but that did not work
Any help or guidance would be really helpful
You could try the following calculated table. I have attached a PBIX file for you to test with.
Table = VAR myFilter = SELECTCOLUMNS( SUMMARIZE( 'Table1', [ID],"Latest Date", MAX('Table1'[Insert Date]) ), "My ID",[ID], "Latest Date",[Latest Date] ) RETURN FILTER(GENERATE(myFilter,'Table1'),[Latest Date]='Table1'[Insert Date] && [My ID] = [ID])
6 Replies
You could try the following calculated table. I have attached a PBIX file for you to test with.
Table = VAR myFilter = SELECTCOLUMNS( SUMMARIZE( 'Table1', [ID],"Latest Date", MAX('Table1'[Insert Date]) ), "My ID",[ID], "Latest Date",[Latest Date] ) RETURN FILTER(GENERATE(myFilter,'Table1'),[Latest Date]='Table1'[Insert Date] && [My ID] = [ID])- Hey Phil,
I am going to try this now, will this have a performance impact compared to a solution in M (if possible even) for the same problem ?No, DAX should be pretty quick
