Forum Discussion
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...
Like this?
Column = CALCULATE ( MAX ( Jen[Date] ), ALLEXCEPT ( Jen, Jen[Process Number], Jen[Department Description] ) )
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
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?
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 UserWithMaxDateHi,
it gives me the error below
- It worked for me even with duplicates, can you please show how your updated date looks like with users.