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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
newgirl
Helper V
Helper V

New Table using Latest Distinct Value

Hi!

 

So I have this table that has consolidated list of client and the corresponding sales representative during the period.

 

ClientSales RepYearMonth
ABarney202001
BTed202001
CRobin202001
DLily202001
AMarshall202002
BTed202002
ERachel202002

 

 

What I need is to create a new table that will consolidate unique values from the client field but show corresponding sales representative based on latest date.

 

Desired new table:

ClientSales Rep
AMarshall
BTed
CRobin
DLily
ERachel

 

1 ACCEPTED SOLUTION

@newgirl I think @amitchandak works if you create it as a measure, then in a table visual add the Client and the Measure.

 

Last Sales Rep = LASTNONBLANKVALUE('Table'[YearMonth],max('Table'[Sales Rep]))

 

lastnonblank.JPG

 

 

 

If you want it as a seperate table, you could also do this:

 

Table 2 = SUMMARIZECOLUMNS('Table'[Client],"Last Sales Rep",[Last Sales Rep])

 

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

@newgirl - Some something like:

Table = 
  VAR __Table =
      ADDCOLUMNS(
        DISTINCT('Table'[Client])
        "Latest",
        MAX('Table'[YearMonth])
      )
  VAR __Table1 =
      __Table,
      "Sales Rep",
      MAXX(FILTER('Table','Table'[Client] = [Client] && 'Table'[YearMonth] = [Latest]),'Table'[Sales Rep])
RETURN
  __Table1

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

Hi, @Greg_Deckler !

 

I tried your measure although I think it lacked a certain DAX formula in the _Table1. I guessed it was ADDCOLUMNS so this is the measure I did:

Table = 
  VAR __Table =
      ADDCOLUMNS(
        DISTINCT('Table'[Client]),
        "Latest",
        MAX('Table'[YearMonth])
      )
  VAR __Table1 =
      ADDCOLUMNS(
      __Table,
      "Sales Rep",
      MAXX(FILTER('Table','Table'[Client] = [Client] && 'Table'[YearMonth] = [Latest]),'Table'[Sales Rep])
      )
RETURN
  __Table1

 

 

However, it returned a table wherein the Client values were indeed unique but in the column for Sales Rep, it shows one Sales Rep value that is the same across all rows.

amitchandak
Super User
Super User

@newgirl , Try like

lastnonblankvalue(Table[YearMonth],max(Table[Sales Rep]))

Hi @amitchandak !

 

I tried your suggested formula in creating a new table but it says "The expression specified in the query is not a valid table expression".

 

I think it's also missing certain fields? Because in the desired table, I need the column for Client and another column for the Sales Rep. 

@newgirl I think @amitchandak works if you create it as a measure, then in a table visual add the Client and the Measure.

 

Last Sales Rep = LASTNONBLANKVALUE('Table'[YearMonth],max('Table'[Sales Rep]))

 

lastnonblank.JPG

 

 

 

If you want it as a seperate table, you could also do this:

 

Table 2 = SUMMARIZECOLUMNS('Table'[Client],"Last Sales Rep",[Last Sales Rep])

 

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

Hi, @DataZoe !

 

Thank you so much for your suggestion. It worked perfectly!

 

@newgirl , for the new table try

summarize(Table, Table[Client], "Sales Rep",lastnonblankvalue(Table[YearMonth],max(Table[Sales Rep])))

 

That was a measure you can use in visual 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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