Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Group different rows into one (same data)

Hi all.   I need to be able to group different rows, which correspond to the same data, into one. In the image you can see that SHIPMENTID and HUUID is the same for the 3 columns, the only differen...
General Comment
Need Help
Tips and Tricks
Tutorial Requests
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Anonymous 
    I though I've had replied back to you with updated solution but for some reason I caanot see my reply. Heree is the sample file https://www.dropbox.com/t/mrD8taPTCHSWO8P2

    Please try this solution if it works. However, this is not a perfect situation for a 17M rows data.

    Shipments Summary 2 = 
VAR T1 = SUMMARIZE ( Shipments, Shipments[ShipmentId], Shipments[HUUID], "Waypoint", MAX ( Shipments[Waypoints] ) )
VAR T2 = 
    GENERATE ( 
        T1, 
        VAR T3 = CALCULATETABLE ( Shipments )
        VAR T4 = 
            ADDCOLUMNS ( 
                T3, 
                "@Rank", RANKX ( FILTER ( T3, [Waypoints_Actual] <> BLANK ( ) ), [Extraction_date] ) 
            )
        VAR FirstReroute = MAXX ( FILTER ( T4, [@Rank] = 2 ), [Waypoints_actual] )
        VAR SecondReroute = MAXX ( FILTER ( T4, [@Rank] = 1 ), [Waypoints_actual] )
        RETURN  
            ROW ( "1st Route", FirstReroute, "2nd Route", SecondReroute,"3rd Route", FirstReroute )
    )
RETURN
    T2
tamerj1's avatar
tamerj1
Community Champion
4 years ago

Anonymous 

Do you want the original Wapoint and Waypoint-Actual columns? Actually I just assumed that you want to keep the one with the longest sting. Maybe this has no meaning. We can skip these unnecessary calculations and save alot of formula engine time. What do you think? Shall I update accordingly? On the other hand I would really recommend using the measure. It takes advantage of the existing filter context this it's more efficient and yet complicately dynamic. It depends on what other measures you are going to create and what is the ultimate report you are looking for but most probably we can solve this out. 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Yes thansk! 
Well i need to keep always the  column " Waypoint" and next to this as many new "waypoint actual " can came.. so if now the row has on 1 waypoint actual.. that would be 1 reroute... if this has 2 so it would have 1 re route and then 2 reroute... and so on.. NEW ACTUAL WAYPOINT= new column based on extraction date of course ..
Like i show it here
Acltual data

Desired data

 

 

 

 

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Anonymous 
    Does the original Waypoints columns allways has onlt one value and the rest are blanks?

    Tn this example you have 2 Waypoints_Actual. The one that you chose to present in the new table was chosen based on what?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Each shipment always has only 1 "Waypoints" and can have multiple "Waypoints_actuals", as the latter appear (based on "extraction date" they must be aligned in new columns ( 1 reroute / 2 reroute / 3 reroute...etc)
      Let me clarify the picture becasu i made a mistake on one of the headears...

      Hope this helps !

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        Anonymous 
        Now you have two "2nd Reroute"!!