topic Re: DAX expression for finding the new row for a given customer in DAX Commands and Tips

DAX expression for finding the new row for a given customer

sumsar10171 — Fri, 10 Feb 2023 12:50:41 GMT

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

Re: DAX expression for finding the new row for a given customer

johnt75 — Fri, 10 Feb 2023 13:59:44 GMT

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 MeetingType

If 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.

Re: DAX expression for finding the new row for a given customer

FreemanZ — Fri, 10 Feb 2023 14:14:56 GMT

hi 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:

Re: DAX expression for finding the new row for a given customer

smpa01 — Fri, 10 Feb 2023 15:19:01 GMT

sumsar10171 with new DAX OFFSET; PFA

Measure = CALCULATE ( [__type], OFFSET ( 1, DISTINCT ( ALL ( tbl ) ), ORDERBY ( tbl[Date], ASC ), KEEP, PARTITIONBY ( tbl[CustomerID] ) ) )

Re: DAX expression for finding the new row for a given customer

sumsar10171 — Fri, 10 Feb 2023 16:08:58 GMT

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

Re: DAX expression for finding the new row for a given customer

sumsar10171 — Fri, 10 Feb 2023 16:16:18 GMT

Is it possible to write this as a column?