Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
MJAGUSIAK
Helper I
Helper I

DAX help: most recent event

‎01-30-2024 03:35 PM

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 event day and time. 

Customer Confirmation NumberDelivery Confirmation NumberManifest DateReceive DateLast Event DateLast Event TimeLast Event DescriptionZip or Postal Code

 

Please let me know how I should go about getting this most recent status. 

I tried to use the following but its not pulling back the latest status:

Last Status =
VAR CustomerNumber = 'DHL Status'[Delivery Confirmation Number]

RETURN
maxx(FILTER(ALL('DHL Status'), 'DHL Status'[Delivery Confirmation Number] = CustomerNumber),
    'DHL Status'[Last Event Description])
Labels:
Message 1 of 20
851 Views
0
1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User
In response to MJAGUSIAK

‎01-31-2024 05:46 PM

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_0-1706751963810.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Message 16 of 20
655 Views
0
19 REPLIES 19
v-kongfanf-msft
Community Support
Community Support

‎01-30-2024 06:55 PM

Hi @MJAGUSIAK ,

 

You can try formula like below:

Last Status = 
VAR CustomerNumber =
    MAX ( 'DHL Status'[Delivery Confirmation Number] )
RETURN
    CALCULATE (
        MAXX (
            FILTER (
                ALL ( 'DHL Status' ),
                'DHL Status'[Delivery Confirmation Number] = CustomerNumber
            ),
            'DHL Status'[Last Event Date] + 'DHL Status'[Last Event Time]
        ),
        FILTER (
            'DHL Status',
            'DHL Status'[Delivery Confirmation Number] = CustomerNumber
        )
    )

vkongfanfmsft_0-1706669682061.png

 

Best Regards,
Adamk Kong

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Message 3 of 20
773 Views
0
MJAGUSIAK
Helper I
Helper I
In response to v-kongfanf-msft

‎01-30-2024 07:19 PM

This formula works for giving me the last date and time that is in the data. 
I need to use the Delivery Confirmation Number and look for the most recent date and then take that most recent date and display whatever is in the column for Last Event Description. 
Basically, I am wanting to override any previously seen status codes and only use the most recent status in the data for that particular customer tracking number. 

Message 4 of 20
772 Views
0
Ashish_Mathur
Super User
Super User
In response to MJAGUSIAK

‎01-30-2024 07:24 PM

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 7 of 20
770 Views
0
MJAGUSIAK
Helper I
Helper I
In response to Ashish_Mathur

‎01-30-2024 07:35 PM
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
       
Message 8 of 20
766 Views
0
Ashish_Mathur
Super User
Super User
In response to MJAGUSIAK

‎01-30-2024 07:38 PM

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 9 of 20
764 Views
0
MJAGUSIAK
Helper I
Helper I
In response to Ashish_Mathur

‎01-31-2024 06:20 AM

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
       
Message 15 of 20
667 Views
0
Ashish_Mathur
Super User
Super User
In response to MJAGUSIAK

‎01-31-2024 05:46 PM

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_0-1706751963810.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 16 of 20
656 Views
0
MJAGUSIAK
Helper I
Helper I
In response to Ashish_Mathur

‎02-27-2024 11:10 AM

Hi Ashish, 

My dev team changed the file for me to use. I need to use the tracking number and the activity date to see the most up to date activity code. I need to see how the M code would be for these columns because I keep getting errors when trying to use the code from above and just insert it into the m code with changing the needed names:

 

 

Warehouse CodeClient NameCarrier CodeShip DateTracking NumberError Desc from CarrierProcess flagActivity CodeActivity Date
73011KS DFW900000112024/02/021ZR637F60107746480 FD20240205

 

Here is my M code that works right now but is not sorted:
let
Source = SharePoint.Files("https://pfsweb0.sharepoint.com/sites/CarrierTracking/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https://pfsweb0.sharepoint.com/sites/CarrierTracking/Web Focus reports/")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (3)", each #"Transform File (3)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (3)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (3)", Table.ColumnNames(#"Transform File (3)"(#"Sample File (3)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Warehouse Code", Int64.Type}, {"Client Name", type text}, {"Carrier Code", Int64.Type}, {"Ship Date", type date}, {"Tracking Number", type text}, {"Error Desc from Carrier", type any}, {"Process flag", type text}, {"Activity Code", type text}, {"Activity Date", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"})
in
#"Removed Columns"

 

Message 19 of 20
548 Views
0
Ashish_Mathur
Super User
Super User
In response to MJAGUSIAK

‎02-27-2024 03:43 PM

Cannot understand what you want.  Share some data to work with, explain the question and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 20 of 20
543 Views
0
MJAGUSIAK
Helper I
Helper I
In response to Ashish_Mathur

‎02-04-2024 11:07 AM

This worked great. much appreciated. 

Message 17 of 20
638 Views
0
Ashish_Mathur
Super User
Super User
In response to MJAGUSIAK

‎02-04-2024 03:18 PM

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 18 of 20
623 Views
0
MJAGUSIAK
Helper I
Helper I
In response to Ashish_Mathur

‎01-30-2024 07:45 PM

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
Message 13 of 20
759 Views
0
Ashish_Mathur
Super User
Super User
In response to MJAGUSIAK

‎01-31-2024 02:51 AM

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 14 of 20
671 Views
0
MJAGUSIAK
Helper I
Helper I
In response to Ashish_Mathur

‎01-30-2024 07:40 PM

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
Message 10 of 20
763 Views
0
Ashish_Mathur
Super User
Super User
In response to MJAGUSIAK

‎01-30-2024 07:47 PM

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]


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 11 of 20
769 Views
0
MJAGUSIAK
Helper I
Helper I
In response to Ashish_Mathur

‎01-30-2024 07:54 PM

The data will have entries with duplicates of Delivery Confirmation Numbers whenever the status of the Last Event Description changes. I need to use the Delivery Confirmation Number and look for the most recent date and then take that most recent date and display whatever is in the column for Last Event Description. 
Basically, I am wanting to override any previously seen status codes and only use the most recent status in the data for that particular customer tracking number. 

 

So if there are duplicate numbers then i need to use the most recent entry to display and use for calculations. 

Message 12 of 20
765 Views
0
lbendlin
Super User
Super User
In response to MJAGUSIAK

‎01-30-2024 07:24 PM

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Message 5 of 20
770 Views
0
MJAGUSIAK
Helper I
Helper I
In response to lbendlin

‎01-30-2024 08:23 PM
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
       
Message 6 of 20
704 Views
0
lbendlin
Super User
Super User

‎01-30-2024 04:39 PM

Your VAR CustomerNumber returns a table.  You may be missing the aggregation function.

 

A simple way would be to use TOPN(1) and CONCATENATEX.

Message 2 of 20
784 Views
0

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

View All