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

Hi AntrikshSharma ,

 

What if I have a one more column with the name of the user like:

Process Number, Date, Department Description, User name.

 

Because now it appears the name of the users who was involved in the process with the same Max Date I want only the last user with the max date.

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    6 years ago

    Try something like this, I have used CONCATENATEX in case if there are duplicates in your table it can be concatenated.

    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

     

    • 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