Forum Discussion
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.
| Client | Sales Rep | YearMonth |
| A | Barney | 202001 |
| B | Ted | 202001 |
| C | Robin | 202001 |
| D | Lily | 202001 |
| A | Marshall | 202002 |
| B | Ted | 202002 |
| E | Rachel | 202002 |
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:
| Client | Sales Rep |
| A | Marshall |
| B | Ted |
| C | Robin |
| D | Lily |
| E | Rachel |
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]))If you want it as a seperate table, you could also do this:
Table 2 = SUMMARIZECOLUMNS('Table'[Client],"Last Sales Rep",[Last Sales Rep])
7 Replies
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 , 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
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 __Table1Hi, 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 __Table1However, 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.
