Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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/...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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's avatar
Anonymous
Not applicable
8 years ago

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's avatar
vanessafvg
Community Champion
8 years ago

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's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    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] )
    )
)
    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      Anonymous

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        Hi Anonymous

         

        Try this revision

         

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