Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JoseBernardo
Helper III
Helper III

Match data based on other column by date

Hi all

 

Im am looking to match diferrent rows in one ( same data) using other column as data parameter to choose the last one. Maybe looking at the example data will be easyer to understand:

Raw data

JoseBernardo_0-1660805031555.png


Desired data: 

JoseBernardo_1-1660805116200.png

 

I need to keep "waypoints" as it is, and next to this i need a new column " LAST REROUTE" wich would be the last "Waypoint actual" based on Extration_date  ( in this case the one for the 3rd of aug).

I was trying something like this but no sucess:


Fact - IR_IM LAST RE_ROUTE =
VAR T1 = SUMMARIZE ( 'Fact - IR_IM RAW DATA', 'Fact - IR_IM RAW DATA'[ShipmentId],'Fact - IR_IM RAW DATA'[Geography],'Fact - IR_IM RAW DATA'[Internaltional/Domestic], 'Fact - IR_IM RAW DATA'[HUUID], "Waypoint", MAX ( 'Fact - IR_IM RAW DATA'[Waypoints]) )
VAR T2 =
    GENERATE (
        T1,
        VAR T3 = CALCULATETABLE ( 'Fact - IR_IM RAW DATA')
        VAR T4 =
            ADDCOLUMNS (
                T3,
                "@Rank", RANKX ( FILTER ( T3, [Waypoints_Actual] <> BLANK ( ) ), [Extraction_date] )
            )
        VAR FirstReroute = MAXX ( FILTER ( T4, [@Rank] = 4 ), [Waypoints_actual] )

RETURN  
            ROW ( "LAST REROUTE", FirstReroute)
    )
RETURN
    T1


@tamerj1 

4 REPLIES 4
PVO3
Impactful Individual
Impactful Individual

Hi JoseBernardo,

 

Looking at your example, I'm wondering what the exact logic is. It appears like you would like you want to keep the most recent [Extraction_date] for earch [ShipmentId]. Only exception is that when [Waypoints] is blank, use a non blank [Waypoints] from the same [ShipmentId]. This should be easily achieved within Power Query.

mm not sure if its that easy, i need to combined the "waypoint" column with the most recent " waypoint actual" based on extraction date, all in one row... not multiples. Take a look at desired result :

JoseBernardo_0-1660806624588.png

 

PVO3
Impactful Individual
Impactful Individual

If that is your logic you can fix it like below. Not the most fancy solution, but you can use the PQ interface.

- Create 2 duplicates of your table in PQ

 

First duplicate:

- Filter out the blank duplicates.

- Merge original with this duplicate on [ShipmentId] 

- Expand [Waypoint] from duplicate

Every [ShipmentId] now has a [Waypoint]

 

Second duplicate:

- Group by [ShipmentId], aggragate on max [Extraction_Date]

- Merge original table with second duplicate

- Expand [Extraction date] from second duplicate

- Create a custom column original.[Extraction_Date] = secondduplicate[Extraction_Date]

- Filter original table by this custom column = TRUE

Most recent [ShipmentId] is filtered

I have over 17 millions rows, i need to avoid going into power query. I did the followin on DAX and resulted correct ( faster aswell):

JoseBernardo_0-1660809358734.png

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors