Forum Discussion
Reference last row values filtered by multiple criteria
Dear Power BI community: I would really appreciate if you could help me out with my following problem. I would like to expand my table with the colums "Last Call" and "Last Employee" by a reference to the last row (matching MachineNo)
I try to fix my problem meanwhile for over two weeks and there ist actually no way to find a solution for me. So please help :)
| CALL_ID | MachineNo | Modell | Call_Date | Last Call | Employee | Last Employee |
| 2908330 | 408427 | KYOC TA 4551CI | 12.12.2018 16:27 | KM | ||
| 2923350 | 408427 | KYOC TA 4551CI | 04.01.2019 10:46 | 12.12.2018 16:27 | KM | KM |
| 2925603 | 408427 | KYOC TA 4551CI | 09.01.2019 10:24 | 04.01.2019 10:46 | BT | KM |
| 2934951 | 426616 | SHAR MXM904 | 23.01.2019 16:07 | SD | ||
| 2956653 | 426616 | SHAR MXM904 | 27.02.2019 10:49 | 23.01.2019 16:07 | DS | SD |
| 2965052 | 426616 | SHAR MXM904 | 14.03.2019 14:41 | 27.02.2019 10:49 | BP | DS |
| 2971818 | 408427 | KYOC TA 4551CI | 26.03.2019 10:21 | 09.01.2019 10:24 | BT | BT |
| 2972146 | 426616 | SHAR MXM904 | 26.03.2019 14:55 | 14.03.2019 14:41 | WH | BP |
| 2989954 | 426616 | SHAR MXM904 | 05.04.2019 11:29 | 26.03.2019 14:55 | WH | WH |
| 3005277 | 426616 | SHAR MXM904 | 07.05.2019 11:51 | 05.04.2019 11:29 | SD | WH |
Hi rennda ,
I created a sample to get the last row values. And you can check if it is what you want.
Last call date = CALCULATE(MAX('Table'[Call_Date]),FILTER(ALLEXCEPT('Table','Table'[MachineNo]),'Table'[Call_Date] <= EARLIER('Table'[Call_Date])-1)) Group rank = RANKX(FILTER('Table','Table'[MachineNo]=EARLIER('Table'[MachineNo])),'Table'[Call_Date],,ASC,Dense) Last Employee = CALCULATE(MAX('Table'[Employee]),FILTER('Table', 'Table'[MachineNo] = EARLIER('Table'[MachineNo]) &&'Table'[Group rank] = EARLIER('Table'[Group rank])-1)) Last call date2 = CALCULATE(MAX('Table'[Call_Date]),FILTER('Table', 'Table'[MachineNo] = EARLIER('Table'[MachineNo]) &&'Table'[Group rank] = EARLIER('Table'[Group rank])-1))I attached my sample that you can download.
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- Cmcmahan
Resident Rockstar
I've got bad news and good news for you. The good news is that this IS doable within DAX. The bad news is that it is not efficient and if you have a large data set will run incredibly slowly.
You can read about somebody who had a similar problem here. Be sure to read through all the replies first, because there was a lot of iteration on different methods for performance for that question. Give it a try, and if you have questions about how to implement the solution for your dataset, feel free to ask follow up questions.
v-xuding-msftCommunity Support
Hi rennda ,
I created a sample to get the last row values. And you can check if it is what you want.
Last call date = CALCULATE(MAX('Table'[Call_Date]),FILTER(ALLEXCEPT('Table','Table'[MachineNo]),'Table'[Call_Date] <= EARLIER('Table'[Call_Date])-1)) Group rank = RANKX(FILTER('Table','Table'[MachineNo]=EARLIER('Table'[MachineNo])),'Table'[Call_Date],,ASC,Dense) Last Employee = CALCULATE(MAX('Table'[Employee]),FILTER('Table', 'Table'[MachineNo] = EARLIER('Table'[MachineNo]) &&'Table'[Group rank] = EARLIER('Table'[Group rank])-1)) Last call date2 = CALCULATE(MAX('Table'[Call_Date]),FILTER('Table', 'Table'[MachineNo] = EARLIER('Table'[MachineNo]) &&'Table'[Group rank] = EARLIER('Table'[Group rank])-1))I attached my sample that you can download.
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
