Reference last row values filtered by multiple criteria

[rennda]

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

[Cmcmahan]

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.

[rennda]

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?  

[v-xuding-msft · Accepted 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.

[rennda]

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.

---