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

     

     

m_dekorte's avatar
m_dekorte
Resident Rockstar
9 months ago

Here's how you can lookup the record with max date, assuming there is only 1 row per date.

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]),
    ChType = Table.TransformColumnTypes(Source,{{"DepartmentID", type text}, {"Date", type date}, {"TransactionID", Int64.Type}}, "en-US"),
    GroupRows = Table.Group(ChType, {"DepartmentID"}, {{"All", each _{[Date=List.Max([Date])]}, type [DepartmentID=nullable text, Date=nullable date, TransactionID=nullable number]}}),
    ExpandFields = Table.ExpandRecordColumn(GroupRows, "All", {"Date", "TransactionID"}, {"Date", "TransactionID"})
in
    ExpandFields

 

alternatively, this will get you all rows with the max date

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]),
    ChType = Table.TransformColumnTypes(Source,{{"DepartmentID", type text}, {"Date", type date}, {"TransactionID", Int64.Type}}, "en-US"),
    GroupRows = Table.Group(ChType, {"DepartmentID"}, {{"All", each Table.SelectRows(_, (x)=> x[Date]=List.Max([Date])), type table [DepartmentID=nullable text, Date=nullable date, TransactionID=nullable number]}}),
    ExpandFields = Table.ExpandTableColumn(GroupRows, "All", {"Date", "TransactionID"}, {"Date", "TransactionID"})
in
    ExpandFields