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

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

    Anonymous

     

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

     

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      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's avatar
        Zubair_Muhammad
        Community Champion
        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
        )
    )