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.
Hi All,
I've been trying to create a caluclated column based on multipul critera for my event table.
I have an event table with features such as Event ID,Customer ID,Event Name, Event Type, Time etc.
I'm trying to reference the previous event to the current event as an extra column called Previous Event which is dependant upon two conditions/columns. Is it the same Customer ID, if so then is it the next in Event after the last Event ID. What Column I want to display is Event Name or Event Type for that previous event if it exists.
The Tables Primary Key is Event ID hence ID -1 one could give me a different customers Event.
What I've done so far:
Previous Row = FILTER(Event, Event[Event ID] = VALUE(Event[Event ID]) - 1)
Thanks in Advance!
Scott
Solved! Go to Solution.
Hi @scottbell,
Suppose your data table is like:
Please try below formula to get previous event.
Previous Event = CALCULATE ( LASTNONBLANK ( 'Event Table'[Event Name], 1 ), FILTER ( 'Event Table', 'Event Table'[Customer ID] = EARLIER ( 'Event Table'[Customer ID] ) && 'Event Table'[Event ID] = EARLIER ( 'Event Table'[Event ID] ) - 1 ) )
Best regards,
Yuliana Gu
Hi @scottbell,
Suppose your data table is like:
Please try below formula to get previous event.
Previous Event = CALCULATE ( LASTNONBLANK ( 'Event Table'[Event Name], 1 ), FILTER ( 'Event Table', 'Event Table'[Customer ID] = EARLIER ( 'Event Table'[Customer ID] ) && 'Event Table'[Event ID] = EARLIER ( 'Event Table'[Event ID] ) - 1 ) )
Best regards,
Yuliana Gu
https://community.powerbi.com/t5/Desktop/reference-Previous-row-using-measures/m-p/725894#M350298
Please help me out on this
@v-yulgu-msft wrote:Hi @scottbell,
Suppose your data table is like:
Please try below formula to get previous event.
Previous Event = CALCULATE ( LASTNONBLANK ( 'Event Table'[Event Name], 1 ), FILTER ( 'Event Table', 'Event Table'[Customer ID] = EARLIER ( 'Event Table'[Customer ID] ) && 'Event Table'[Event ID] = EARLIER ( 'Event Table'[Event ID] ) - 1 ) )Best regards,
Yuliana Gu
@v-yulgu-msft wrote:Hi @scottbell,
Suppose your data table is like:
Please try below formula to get previous event.
Previous Event = CALCULATE ( LASTNONBLANK ( 'Event Table'[Event Name], 1 ), FILTER ( 'Event Table', 'Event Table'[Customer ID] = EARLIER ( 'Event Table'[Customer ID] ) && 'Event Table'[Event ID] = EARLIER ( 'Event Table'[Event ID] ) - 1 ) )Best regards,
Yuliana Gu
Thanks Yuliana,
Changing the column names and it works perfectly 🙂
I'm assuming I can add extra conditions like this? Because Customers may belong to multipul Orgs and be using it across their systems so I want to limit to their Org context.
FILTER ( 'Event Table', 'Event Table'[Customer ID] = EARLIER ( 'Event Table'[Customer ID] ) && 'Event Table'[Event ID] = EARLIER ( 'Event Table'[Event ID] ) - 1 &&'Event Table'[Org ID] = EARLIER('Event Table'[Org ID]) -1 )
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 |
---|---|
107 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |