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

See when key Fabric features will launch and what’s already live, all in one place and always up to date. Explore the new Fabric roadmap

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


Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

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


Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
May PBI 25 Carousel

Power BI Monthly Update - May 2025

Check out the May 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

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