cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tvcooper
Frequent Visitor

Obtaining text value from last transaction per customer

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??

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
tvcooper
Frequent Visitor

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

Greg_Deckler
Super User
Super User

@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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors