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

    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