Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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!:
The Definitive Guide to Power Query (M)

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors