Forum Discussion

MJAGUSIAK's avatar
MJAGUSIAK
Icon for Helper I rankHelper I
2 years ago
Solved

DAX help: most recent event

I am trying to find the most recent event in my PBI.  Here are my column names and I need to find the last status update under "Last Event Description". I created a new column that combines the last...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    Hi,

    This M code works

    let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Customer Confirmation Number", "Delivery Confirmation Number"}, {{"Count", each Table.Max(_,{"Last Event Date","Last Event Time"})}}),
    #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"Manifest Date", "Receive Date", "Last Event Date", "Last Event Time", "Last Event Description"}, {"Manifest Date", "Receive Date", "Last Event Date", "Last Event Time", "Last Event Description"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Count",{{"Last Event Date", type date}, {"Last Event Time", type time}})
in
    #"Changed Type"

    Hope this helps.

     

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
2 years ago

Hi,

Share data in a format that can be pasted in an MS Excel file.  Show the expected result as well.

MJAGUSIAK's avatar
MJAGUSIAK
Icon for Helper I rankHelper I
2 years ago
Customer Confirmation NumberDelivery Confirmation NumberManifest DateReceive DateLast Event DateLast Event TimeLast Event Description
'78510745'92748903261399047233141/25/2024 5:071/22/2024 19:021/26/20246:10:00 AMOUT FOR DELIVERY
'78542345'92748903261399047394521/25/2024 5:071/23/2024 18:581/26/20246:21:00 AMOUT FOR DELIVERY
'78510884'92748903261399047361611/25/2024 5:071/23/2024 18:581/26/20246:55:00 AMOUT FOR DELIVERY
'78541001'92612903261399047379401/25/2024 1:081/23/2024 18:591/26/20246:10:00 AMOUT FOR DELIVERY
'78510745'92748903261399047233141/25/2024 5:071/22/2024 19:021/26/20246:10:00 AMDELIVERED
'78542345'92748903261399047394521/25/2024 5:071/23/2024 18:581/26/20246:21:00 AMDELIVERED
'78510884'92748903261399047361611/25/2024 5:071/23/2024 18:581/26/20246:55:00 AMDELIVERED
'78541001'92612903261399047379401/25/2024 1:081/23/2024 18:591/26/20246:10:00 AMDELIVERED
       
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Based on the Table that you have shared, please also show the expected result.

    • MJAGUSIAK's avatar
      MJAGUSIAK
      Icon for Helper I rankHelper I
      2 years ago

      The expected result is the following and it should not show back the other rows:

      '78541001'92612903261399047379401/25/2024 1:081/23/2024 18:591/26/20246:10:00 AMDELIVERED
      '78541001'92612903261399047379401/25/2024 1:081/23/2024 18:591/26/20246:10:00 AMDELIVERED
      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        2 years ago

        Hi,

        "Last Event Description" is a column with text entries.  So what do you mean by "Find the last status update"? [from your first post]

    • MJAGUSIAK's avatar
      MJAGUSIAK
      Icon for Helper I rankHelper I
      2 years ago

      It should pull back the data where it is showing only a unique value for Delivery Confirmation Number with the most recent "Last date and time" showing the "last Event Description"

      Customer Confirmation NumberDelivery Confirmation NumberManifest DateReceive DateLast Event DateLast Event TimeLast Event Description
      '78510745'92748903261399047233141/25/2024 5:071/22/2024 19:021/26/20246:10:00 AMDELIVERED
      '78542345'92748903261399047394521/25/2024 5:071/23/2024 18:581/26/20246:21:00 AMDELIVERED
      '78510884'92748903261399047361611/25/2024 5:071/23/2024 18:581/26/20246:55:00 AMDELIVERED
      '78541001'92612903261399047379401/25/2024 1:081/23/2024 18:591/26/20246:10:00 AMDELIVERED
      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        2 years ago

        9274890326139904723314 has 2 entries and both have the same end date and time.  Why shoud the delivered row show up in the result. 

    • MJAGUSIAK's avatar
      MJAGUSIAK
      Icon for Helper I rankHelper I
      2 years ago

      Sorry, didnt catch that when I created the sample data. please use below. 

      Customer Confirmation NumberDelivery Confirmation NumberManifest DateReceive DateLast Event DateLast Event TimeLast Event Description
      '78510745'92748903261399047233141/25/2024 5:071/22/2024 19:021/25/20246:10:00 AMOUT FOR DELIVERY
      '78542345'92748903261399047394521/25/2024 5:071/23/2024 18:581/25/20246:21:00 AMOUT FOR DELIVERY
      '78510884'92748903261399047361611/25/2024 5:071/23/2024 18:581/25/20246:55:00 AMOUT FOR DELIVERY
      '78541001'92612903261399047379401/25/2024 1:081/23/2024 18:591/25/20246:10:00 AMOUT FOR DELIVERY
      '78510745'92748903261399047233141/25/2024 5:071/22/2024 19:021/26/20246:10:00 AMDELIVERED
      '78542345'92748903261399047394521/25/2024 5:071/23/2024 18:581/26/20246:21:00 AMDELIVERED
      '78510884'92748903261399047361611/25/2024 5:071/23/2024 18:581/26/20246:55:00 AMDELIVERED
      '78541001'92612903261399047379401/25/2024 1:081/23/2024 18:591/26/20246:10:00 AMDELIVERED
             
      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        2 years ago

        Hi,

        This M code works

        let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Customer Confirmation Number", "Delivery Confirmation Number"}, {{"Count", each Table.Max(_,{"Last Event Date","Last Event Time"})}}),
    #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"Manifest Date", "Receive Date", "Last Event Date", "Last Event Time", "Last Event Description"}, {"Manifest Date", "Receive Date", "Last Event Date", "Last Event Time", "Last Event Description"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Count",{{"Last Event Date", type date}, {"Last Event Time", type time}})
in
    #"Changed Type"

        Hope this helps.