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/...
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 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
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
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
Hi Anonymous
Try this revision
LastSalesRep = CALCULATE ( FIRSTNONBLANK ( TableName[Sales Rep], 1 ), FILTER ( TableName, TableName[Last order date] = MAX ( TableName[Last order date] ) ) )Anonymous
Error might be due to the fact that there could be 2 sales rep on the same latest date