Unique column values

[Anonymous]

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 

[Zubair_Muhammad]

@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

[vanessafvg]

@Anonymous just create a dax measure do a LastDate = lastdate(datefield) and use the LastDate

[Anonymous]

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

[vanessafvg]

@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

[Zubair_Muhammad]

@Anonymous

Try this MEASURE.
Then Drag Customer # and this MEASURE to  a TABLE visual

LastSalesRep =
CALCULATE (
    SELECTEDVALUE ( TableName[Sales Rep] ),
    FILTER (
        TableName,
        TableName[Last order date] = MAX ( TableName[Last order date] )
    )
)

[Anonymous]

Hi @Zubair_Muhammad

This looks promissing however i get this error for some reason 

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

what can be this linked to  

[Zubair_Muhammad]

Hi @Anonymous

Try this revision

LastSalesRep =
CALCULATE (
    FIRSTNONBLANK ( TableName[Sales Rep], 1 ),
    FILTER (
        TableName,
        TableName[Last order date] = MAX ( TableName[Last order date] )
    )
)

[Zubair_Muhammad]

@Anonymous

Error might be due to the fact that there could be 2 sales rep on the same latest date

[Zubair_Muhammad]

@Anonymous

Alternatively you could use this formula as well

As a MEASURE

LastSalesRep_ =
CALCULATE (
    CONCATENATEX (
        FILTER (
            TableName,
            TableName[Last order date] = MAX ( TableName[Last order date] )
        ),
        TableName[Sales Rep],
        ", "
    )
)

[Zubair_Muhammad · Accepted solution]

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

[Anonymous]

hi @Zubair_Muhammad

THe measures work perfectly but the column actually doesnt, It will always show only one person and thats i guess the sales person that placed the order last and is first in the alpabetical order.  

Only if I use the CONCATENATEX version i get the right result but then there are the other results behind it in case of same order date..

How can we modify the calculate column formlua that way that it will get the last sales rep per customer/account .. so what the measure does but as a column 

Thank you 

[Zubair_Muhammad]

@Anonymous