Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
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.
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.
Hey @Cmcmahan ,
thanks for your reply. I tried it to read the link you have posted, but it seems to be to much information to me as a Power BI Rookie 🙂
Is it possible to get the Name from the EMPLOYEE from the last TicketNo by the EARLIER function?
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.
WOW! Thanks to @v-xuding-msft for this fast solution. So i could diving into the weekend now. Phenomanally!
@v-xuding-msft wrote: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.
@v-xuding-msft wrote: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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |