Forum Discussion
DAX expression for finding the new row for a given customer
Hi,
I'm trying to write a dax expression which will give me a new column for my table "meetings".
It's suppose to fetch the meeting type for the next meeting for the client.
Current table:
| ID | CustomerID | Date | Type |
| 1 | 1 | 2023-02-01 | Startup |
| 2 | 2 | 2023-02-01 | Retention |
| 3 | 3 | 2023-02-01 | Startup |
| 4 | 2 | 2023-02-05 | Support |
| 5 | 1 | 2023-02-05 | Adjustment |
What I'm looking for:
| ID | CustomerID | Date | Type | Next meeting type |
| 1 | 1 | 2023-02-01 | Startup | Adjustment |
| 2 | 2 | 2023-02-01 | Retention | Support |
| 3 | 3 | 2023-02-01 | Startup | |
| 4 | 2 | 2023-02-05 | Support | |
| 5 | 1 | 2023-02-05 | Adjustment |
5 Replies
johnt75Super User
You can create a column like
Next meeting type = VAR NextMeeting = OFFSET( 1, 'Table', ORDERBY( 'Table'[Date], ASC), PARTITIONBY( 'Table'[CustomerID]) ) VAR MeetingType = SELECTCOLUMNS( NextMeeting, "Type", 'Table'[Type]) RETURN MeetingTypeIf it complains that there may be duplicate rows you'll need to go into the modelling view, select the table and choose ID as the key column.
FreemanZhi sumsar10171
try like:
Column = MINX( TOPN( 1, FILTER( TableName, TableName[CustomerID] = EARLIER(TableName[CustomerID]) &&TableName[Date] > EARLIER(TableName[Date]) ), TableName[Date], DESC ), TableName[Type] )it worked like:
It doesn't account for customers with more then two meetings.
In that case it will simply take the type of the latest meeting and apply to all previous meetings
smpa01Community Champion
sumsar10171 with new DAX OFFSET; PFA
Measure = CALCULATE ( [__type], OFFSET ( 1, DISTINCT ( ALL ( tbl ) ), ORDERBY ( tbl[Date], ASC ), KEEP, PARTITIONBY ( tbl[CustomerID] ) ) )Is it possible to write this as a column?
