Rows - Maximum Date

Hi jerryr 

Can you please try the below DAX

I have created the measure using below dax .

Show Latest =

IF (

    MAX ( data[Date] ) =

        CALCULATE (

            MAX ( data[Date] ),

            ALLEXCEPT ( data, data[DepartmentID] )

        ),

    1,

    0

)

 

Create a table visual and add all the column u want in as it shows in your result

then click on the visual and then go for visual level filter ( Set Show Latest = 1).

 

 

If this answers your questions, kindly accpet it as a solution and give kudos.

Hi jerryr 

Using Power Query/M you can do this with List.PositionOf to find the max date then extract the Date and Transaction ID.

First, Group By DepartmentID

Then create a Custom Column and use this to get the Date

= [All][Date]{List.PositionOf([All][Date], List.Max([All][Date]))}

and another Custom Column for the TransactionID

= [All][TransactionID]{List.PositionOf([All][Date], List.Max([All][Date]))}

Here's the full code and you can download it in this PBIX file

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc1RCsAgDAPQu/RbaNpa3O+2Y0jvfw07FRnkJzxCeqebCkHYhBXqWUSNoiwQYRyw6hOeD8CZDV7tAIwzG1q7Jrxrgd9Ho4gB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DepartmentID = _t, Date = _t, TransactionID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DepartmentID", type text}, {"Date", type text}, {"TransactionID", Int64.Type}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-US"),
    #"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"DepartmentID"}, {{"All", each _, type table [DepartmentID=nullable text, Date=nullable date, TransactionID=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Date", each [All][Date]{List.PositionOf([All][Date], List.Max([All][Date]))}),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "TransactionID", each [All][TransactionID]{List.PositionOf([All][Date], List.Max([All][Date]))}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"All"})
in
    #"Removed Columns"

Regards

Phil