Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Latest Date

Hi All,   I have a table with the structure below:   Process Number Date Department Description 354152 05/10/2019 10:24:20 AM IT 354152 05/10/2019 10:29:10 AM Business 354152 0...
Need Help
  • AntrikshSharma's avatar
    AntrikshSharma
    6 years ago

    Like this?

    Column =
CALCULATE (
    MAX ( Jen[Date] ),
    ALLEXCEPT ( Jen, Jen[Process Number], Jen[Department Description] )
)
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thank you Antriksh, it worked but it shows multiple lines with the last user and what I wanted is just a single line with the latest date, process number, department, user.

 

There is no other way to do that?

AntrikshSharma's avatar
AntrikshSharma
Community Champion
6 years ago

try this:

Column =
VAR MaxDate =
    CALCULATE (
        MAX ( Jen[Date] ),
        ALLEXCEPT ( Jen, Jen[Process Number], Jen[Department Description] )
    )
VAR UserWithMaxDate =
    SELECTCOLUMNS (
        FILTER ( ALL ( Jen[Date], Jen[User] ), Jen[Date] = MaxDate ),
        "Users", Jen[User]
    )
RETURN
    UserWithMaxDate
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi,

     

    it gives me the error below

     

     

    • AntrikshSharma's avatar
      AntrikshSharma
      Community Champion
      6 years ago
      It worked for me even with duplicates, can you please show how your updated date looks like with users.
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi,

         

         

        DateMax code:

        Column =
CALCULATE (
    MAX ( Jen[Date] ),
    ALLEXCEPT ( Jen, Jen[Process Number], Jen[Department Description] )
)

         

        Column code:

        Column =
VAR ProcessNum = Jen[Process Number]
VAR Dept = Jen[Department Description]
VAR F =
    FILTER (
        ALL ( Jen ),
        Jen[Process Number] = ProcessNum
            && Jen[Department Description] = Dept
    )
VAR MaxDate =
    CALCULATE ( MAX ( Jen[Date] ), F )
VAR UsersWithMaxDate =
    SELECTCOLUMNS ( FILTER ( F, Jen[Date] = MaxDate ), "Users", Jen[User] )
VAR NumOfUser =
    COUNTROWS ( UsersWithMaxDate )
VAR Result =
    IF (
        NumOfUser > 1,
        CONCATENATEX ( UsersWithMaxDate, [Users], ", " ),
        UsersWithMaxDate
    )
RETURN
    Result