Forum Discussion
Helper ITo show previous value based another column in DAX
Hello !
I have a big set of data and need to show in the column "Previous agent" the agent who took the previous call from the same phone number, please see the example below:
| Date | Phone number | Agent | Call Attempt | Previous Agent |
| 13-12-23 16:02 | 11111 | Agent 1 | 1 | |
| 14-12-23 16:03 | 11111 | Agent 2 | 2 | Agent 1 |
| 27-12-23 13:49 | 11111 | Agent 3 | 3 | Agent 2 |
| 28-12-23 13:18 | 22222 | Agent 3 | 1 | |
| 29-12-23 11:49 | 22222 | Agent 2 | 2 | Agent 3 |
| 29-12-23 12:49 | 33333 | Agent 2 | 1 | |
| 29-12-23 15:42 | 33333 | Agent 1 | 2 | Agent 2 |
I've found solution for this, it works, but it is very slow... Almost impossible to work when i have big volumes of data (currently i have 34 000 rows).
Here is the solution i've found:
Could you, please, help me with alternative in DAX that is faster ?
Thank you very much !
Hi,
Write this calculated column formula
Column = LOOKUPVALUE(Data[Agent],Data[Date],CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Phone]=EARLIER(Data[Phone])&&Data[Date]<EARLIER(Data[Date]))),Data[Phone],Data[Phone])Hope this helps.
6 Replies
- Greg_Deckler
Community Champion
Dimi_2207 Try a calculated column perhaps? Generally looks like MTBF without the final step, you just want the previous value, not a difference or any calculation. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous - Ashish_Mathur
Super User
Hi,
This should ideally be solved with a calculated column formula
Previous agent = CALCULATE(MAX(Data[Agent]),FILTER(Data,Data[Phone number]=EARLIER(Data[Phone number])&&Data[Date]<EARLIER(Data[Date])))Hope this helps.
Dimi_2207Thank you very much, it works great and quickly with the initial sample i've provided, where i have: Agent 1, Agent 2, Agent 3...
Actually, i have names in that column (sorry, i had to be more precise from the start!)
And, in this case, this does not work...Please, see, updated sample size.
Expectation:
Phone Date Agent Previous agent 111 08-11-23 08:24 Kate 111 08-11-23 09:48 Alex Kate 111 08-11-23 10:38 Zara Alex 111 08-11-23 10:57 Michael Zara 111 08-11-23 14:36 Nataly Michael 111 08-11-23 15:54 Yohan Nataly 111 09-11-23 08:38 Sofia Yohan 111 09-11-23 10:14 Sofia Sofia 111 09-11-23 10:15 Rik Sofia 222 13-12-23 16:02 Rik 222 14-12-23 16:03 Amanda Rik 333 22-12-23 19:25 Maria 333 23-12-23 18:54 Cyril Maria 333 23-12-23 19:01 Ivan Cyril 222 27-12-23 13:49 Nikolas Amanda 333 28-12-23 08:56 Elena Ivan What Power BI shows:
- Ashish_Mathur
Hi,
Write this calculated column formula
Column = LOOKUPVALUE(Data[Agent],Data[Date],CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Phone]=EARLIER(Data[Phone])&&Data[Date]<EARLIER(Data[Date]))),Data[Phone],Data[Phone])Hope this helps.
