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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

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.

Anonymous
Not applicable

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

Anonymous
Not applicable

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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