topic Re: Obtaining text value from last transaction per customer in DAX Commands and Tips

Obtaining text value from last transaction per customer

tvcooper — Tue, 30 May 2023 17:31:39 GMT

I have a pretty simple model. I have a SALE table, a Customer Table, a Code Table and a Calendar. All one to many relationships.

Customer table: (subcustnum = sale[subcustnum])

CustomerNum Customer Name SubCustomerNum SubCustName

1 Smith 123 Mary

1 Smith 128 Bob

2 Jones 114 Hortence

CODE table: (Code = Sale[Transcode])

Code CodeGroup

CD Delivery

CF Delivery

FP Service

TT Service

LG Other

SALE Table:

Customer Num SubCustomer Transaction Num TransCode TransDate TransSeq

1 123 12354 CD 3/1/22 1

1 128 88432 FP 4/8/23 3

1 123 63952 LG 4/8/23 2

2 114 33684 TT 5/1/20 1

2 114 55532 CD 6/8/22 2

I want to pull the CodeGroup from Code table for the last transaction for each customer from sale table. Result should look like this:

Customer Name TransDate CodeGroup

Smith 4/8/23 Service

Jones 6/8/22 Delivery

My formula is:

LastCode =
VAR MaxSeq = Max(Sale[TransDate])

VAR CodeName = Calculate(Max(Code[group]),Sale[TransDate]>=MaxSeq

Return

CodeName

That resulted in the error cannot convert value 'deliver' of type text to type number.

So I modified from max(code[group]) to values(code[group]) and got "a table of multiple values was supplied where a single value was expected"

So to test the parts,I returned "MaxSeq" (instead of Code Name). Weirdly, I got a negative value. The number was correct, but all the numbers in seq are positive, so I don't understand why I got negative numbers. (THIS IS QUESTION ONE).

Question two -and my main question, is how do I pull this darn code??

Re: Obtaining text value from last transaction per customer

Greg_Deckler — Tue, 30 May 2023 19:14:54 GMT

tvcooper Try:

LastCode = VAR __CustomerNum = MAX('Customer'[CustomerNum]) VAR __MaxSeq = Max(Sale[TransDate]) VAR __TransCode = MAXX(FILTER('Sale', [Customer Num] = __CustomerNum && [TransDate] = __MaxSeq), [TransCode]) VAR __Result = MAXX(FILTER('CODE', [Code] = __TransCode), [CodeGroup]) RETURN __Result

Re: Obtaining text value from last transaction per customer

tvcooper — Tue, 30 May 2023 20:59:11 GMT

Greg_Deckler Thank you for the help. This worked great. I'm dealing with another issue where my sequence isn't unique. (Its a calculated column and I don't have enough unique markers to derive a unique sequence). In the case where my sequence isn't unique, I"m getting blank results. (I can't use date because date isn't unique and in some cases isn't the order of the transactions.