Forum Discussion

jerryr's avatar
jerryr
New Member
8 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
    8 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
    8 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

     

     

Chewdata's avatar
Chewdata
Responsive Resident
8 months ago

Hey,

This method checks against the max date for that apartment ID and then for the largest Transaction ID. If there are multiple records for that date for that departmentID. If you remove the filter_TransactionID, you get filtering only on date.

 

let
    // replace YOURDATA with your source
    Source = YOURDATA,

    // select rows that match the Max date for that DepartmentID
    filter_maxDate = Table.SelectRows(
        Source, 
        each ([Date] = List.Max(Table.SelectRows(Source, (Row) => Row[DepartmentID] = _[DepartmentID])[Date]))
        ),

    // select rows that match the Max transactionID for the DepartmentID
    filter_maxTransactionID = Table.SelectRows(
        filter_maxDate, 
        each ([TransactionID] = List.Max(Table.SelectRows(filter_maxDate, (Row) => Row[DepartmentID] = _[DepartmentID])[TransactionID]))
        )
        
in
    filter_maxTransactionID

 

Hope this helps!
If any of these solutions helps you, please consider to give kudo's and accept an answer so other users with similar problems can find the answer quickly!