Forum Discussion

jerryr's avatar
jerryr
New Member
9 months ago
Solved

Rows - Maximum Date

Hi - I have a table and I woudl like to only keep the rows with the maximum date by a specific column. Example: Starting data : TableTransactions DepartmentID Date TransactionID A 01/31/...
Show and Tell
Tips and Tricks
  • mdaatifraza5556's avatar
    mdaatifraza5556
    9 months ago

    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.
  • PhilipTreacy's avatar
    PhilipTreacy
    9 months ago

    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

     

     

Zanqueta's avatar
Zanqueta
Super User
9 months ago

Hello jerryr,

You can also do this in the PowerQuery query editor using Table.Group function:


Here my simulation script for your case:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Rc1LCsAwCATQu7gO+IuYbdtjBO9/jdpEUpiNvkHnhAsaKCMxConlwKIQbUNu+YB2W3B/JcJMgXU9QIqZAvex4KlT9P9wiHgB", 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,{{"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"DepartmentID"}, {{"LastDate", each List.Max([Date]), type nullable date}, {"TransactionID", each List.Max([TransactionID]), type nullable text}})
in
    #"Grouped Rows"

 

✅ If this response resolved your issue, please mark it as correct to assist other members of the community.