Forum Discussion
Unique column values
Hi
I am trying to get a table wth cst and sales rep based on the last order date
so I have a table
| Customer # | Sales Rep | Last order date |
| 1018574 | tim | 9/21/2017 |
| 1018574 | alex | 9/22/2017 |
| 1017640 | dirk | 1/12/2018 |
| 1017640 | pit | 8/23/2017 |
| 1017640 | john | 10/24/2017 |
| 1020601 | kris | 1/11/2018 |
| 1020601 | michael | 12/15/2017 |
| 1015972 | Lisa | 1/11/2018 |
| 1015972 | carsten | 1/15/2018 |
| 1015972 | maria | 9/4/2017 |
| 1015972 | tom | 12/28/2017 |
| 1015981 | carsten | 11/13/2017 |
| 1015981 | niels | 1/15/2018 |
| 1020722 | jenny | 12/1/2017 |
| 1020722 | peter | 1/4/2018 |
and I would like to get a new table which will look like this
| 1018574 | alex |
| 1017640 | dirk |
| 1020601 | kris |
| 1015972 | carsten |
| 1015981 | niels |
| 1020722 | peter |
thank you
Anonymous
As a calculated column you could use
Column = VAR MyMax = CALCULATE ( MAX ( TableName[Last order date] ), ALLEXCEPT ( TableName, TableName[Customer #] ) ) RETURN CALCULATE ( FIRSTNONBLANK ( TableName[Sales Rep], 1 ), FILTER ( ALLEXCEPT ( TableName, TableName[Customer #] ), TableName[Last order date] = MyMax ) )
12 Replies
Anonymous just create a dax measure do a LastDate = lastdate(datefield) and use the LastDate
Thanx vanessafvg
That doesnt work because i have multiple sales reps per customer.
Example customer
1015970 was maintained by sales rep maria in sept 2017, by tom in december 2017, by lisa on the 11th of Jan and finaly by Tom on 15th of Jan 2018
I would like to see only TOM
Thats why i am trying to get a column where i will see the last date per customer and then use it in another column where i would liek to use if function or something like that..
would be great if you can help me somehow becasue i am really strugeling here..
thanx for your time
Anonymous ok you need to summarzie the table in my opinion
summarizetable = SUMMARIZE(data,Data[Customer #], Data[Sales Rep], "Lastdate", max(Data[Last order date]) )
from the new table you should be abel to pull the last per record
Anonymous
It works when I use the sample data you have provided. See the pic below
Could you show me a screenshot of your formula?
or share the file via onedrive or googledrive
