cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
newgirl
Helper IV
Helper IV

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,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User IV
Super User IV

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

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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 IV
Super User IV

@newgirl , Try like

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

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 

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors