Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Merge based on multiple conditions

Hi all,

 

I am struggling with this one, would really really appreciate any help I can get.

I want to merge the sample data set into a new table 
Sample Dataset - here 

 

Expected Result 


So logic here basically is that this A ID - A1 has the same 1000 units which got affected for the third time at the same time but they were closed in different segments hence you see 500, 300, 200 
Diff1 in expected result is total of current diff1 plus previous 2 diff1 of sample data 
Can I please get the formula to get the expected result? and this should be in the desktop not PQ. 

Need Help
  • shafiz_p's avatar
    shafiz_p
    1 year ago

    Hi Anonymous , Not very clear what you want to achieve. However, tried to develop expected result. 

     

    Try this code to create new table. Need to have or create necessary columns in the data set.

     

    ExpectedResult = 
VAR MinAID = MINX(FILTER('Table', 'Table'[GroupID] <> BLANK()), 'Table'[A ID])
VAR GPIDBlankData = FILTER('Table', ISBLANK('Table'[GroupID]))
VAR MinOpen = MINX('Table', 'Table'[Open])
VAR CurrentDiff = MAXX(GPIDBlankData, [diff])

VAR SumDiffFC92B7 = 
    SUMX(
        ADDCOLUMNS(
            FILTER('Table', 'Table'[B ID] = "FC92B7"),
            "Difference", 
            VAR CurrentOpen = 'Table'[Open]
            VAR PreviousClose = MAXX(FILTER('Table', 'Table'[B ID] = "FC92B7" && 'Table'[Open] < EARLIER('Table'[Open])), 'Table'[Close])
            RETURN IF(ISBLANK(PreviousClose), DATEDIFF(TIME(0, 0, 0), CurrentOpen, SECOND), DATEDIFF(PreviousClose, CurrentOpen, SECOND))
        ), 
        [Difference]
    ) + CurrentDiff

VAR ResultTable = 
    ADDCOLUMNS(
        GPIDBlankData,
        "Incident A", MinAID,
        "Opening", MinOpen,
        "Close Adjusted", (HOUR('Table'[Close]) * 3600 + MINUTE('Table'[Close]) * 60 + SECOND('Table'[Close])) - SumDiffFC92B7,
        "Affected Unit", 'Table'[Affected Units]
    )

VAR FormattedResultTable = 
    ADDCOLUMNS(
        ResultTable,
        "FormattedDiff", 
        VAR TotalSeconds = [Close Adjusted]
        VAR Hours = INT(TotalSeconds / 3600)
        VAR Minutes = INT(MOD(TotalSeconds, 3600) / 60)
        VAR Seconds = MOD(TotalSeconds, 60)
        RETURN IF(
            Hours = 0, 
            Minutes & " minutes " & Seconds & " seconds", 
            Hours & " hour " & Minutes & " minutes " & Seconds & " seconds"
        )
    )

VAR _return =
SELECTCOLUMNS(
    FormattedResultTable,
    "Incident A", [Incident A],
    "B ID", [B ID],
    "Open", FORMAT([Opening], "hh:mm:ss"),
    "Close", FORMAT([Close], "hh:mm:ss"),
    "Diff", [FormattedDiff],
    "Affected Units", [Affected Unit]
)

RETURN

SELECTCOLUMNS(
    _return,
    [Incident A],
    [B ID],
    [Open],
    [Close],
    [Diff],
    [Affected Units]
)

     

     

    Check out the attached file here for better review and understanding.

     

    Try to change table and column name accordingly. Try implement your own condition based on this.

     

    Hope this helps!!

4 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi Anonymous  - you can create a calculated table and use summarize function as below: 

     

    I am attaching pbix FYR; Please check

     

     

     

    MergedTable =
    SUMMARIZE (
        'Megdata',
        'Megdata'[A ID],
        'Megdata'[B ID],
        'Megdata'[Open],
        'Megdata'[Close],
        "Total Affected Units", SUM('Megdata'[Affected Units])
    )
     
    for diff1
     

     

    Diff1 =
    VAR OpenTime = TIMEVALUE(MergedTable[Open])
    VAR CloseTime = TIMEVALUE(MergedTable[Close])
    VAR DiffSeconds = DATEDIFF(OpenTime, CloseTime, SECOND)

    RETURN
        IF(DiffSeconds < 60,
            FORMAT(DiffSeconds, "0") & " seconds",
            IF(DiffSeconds < 3600,
                FORMAT(INT(DiffSeconds / 60), "0") & " minutes " & MOD(DiffSeconds, 60) & " seconds",
                FORMAT(INT(DiffSeconds / 3600), "0") & " hours " & FORMAT(MOD(DiffSeconds, 3600) / 60, "0") & " minutes"
            )
        )
     
    Hope this works and helps.
     
    Hope this works. 
    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Thank rajendraongole1 for your quick response but the merged table isn't the same as what is expected, if you look at the expected table closely , you'll see I am expecting min value of AID in first column and then in second column all 3 BID of z9j0x2 in same sequence, in third Column min of Open, in fourth column, same time of all 3 BID of z9j0x2, in fifth column there's a calculation so close time minus 5 seconds which is basically sum of previous 2 diff2 values, or here I can take sum of current Diff and 2 Diff Value of FC92B7, it will give the same result so whichever is easy, and then last column is value of affected units of all 3 BID of z9j0x2 in sequence
      I am sorry if this is a bit confusing but you can refer the image of Expected result

       



  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    shafiz_p Hi sir, would you be able to help here please?

  • shafiz_p's avatar
    shafiz_p
    Super User
    1 year ago

    Hi Anonymous , Not very clear what you want to achieve. However, tried to develop expected result. 

     

    Try this code to create new table. Need to have or create necessary columns in the data set.

     

    ExpectedResult = 
VAR MinAID = MINX(FILTER('Table', 'Table'[GroupID] <> BLANK()), 'Table'[A ID])
VAR GPIDBlankData = FILTER('Table', ISBLANK('Table'[GroupID]))
VAR MinOpen = MINX('Table', 'Table'[Open])
VAR CurrentDiff = MAXX(GPIDBlankData, [diff])

VAR SumDiffFC92B7 = 
    SUMX(
        ADDCOLUMNS(
            FILTER('Table', 'Table'[B ID] = "FC92B7"),
            "Difference", 
            VAR CurrentOpen = 'Table'[Open]
            VAR PreviousClose = MAXX(FILTER('Table', 'Table'[B ID] = "FC92B7" && 'Table'[Open] < EARLIER('Table'[Open])), 'Table'[Close])
            RETURN IF(ISBLANK(PreviousClose), DATEDIFF(TIME(0, 0, 0), CurrentOpen, SECOND), DATEDIFF(PreviousClose, CurrentOpen, SECOND))
        ), 
        [Difference]
    ) + CurrentDiff

VAR ResultTable = 
    ADDCOLUMNS(
        GPIDBlankData,
        "Incident A", MinAID,
        "Opening", MinOpen,
        "Close Adjusted", (HOUR('Table'[Close]) * 3600 + MINUTE('Table'[Close]) * 60 + SECOND('Table'[Close])) - SumDiffFC92B7,
        "Affected Unit", 'Table'[Affected Units]
    )

VAR FormattedResultTable = 
    ADDCOLUMNS(
        ResultTable,
        "FormattedDiff", 
        VAR TotalSeconds = [Close Adjusted]
        VAR Hours = INT(TotalSeconds / 3600)
        VAR Minutes = INT(MOD(TotalSeconds, 3600) / 60)
        VAR Seconds = MOD(TotalSeconds, 60)
        RETURN IF(
            Hours = 0, 
            Minutes & " minutes " & Seconds & " seconds", 
            Hours & " hour " & Minutes & " minutes " & Seconds & " seconds"
        )
    )

VAR _return =
SELECTCOLUMNS(
    FormattedResultTable,
    "Incident A", [Incident A],
    "B ID", [B ID],
    "Open", FORMAT([Opening], "hh:mm:ss"),
    "Close", FORMAT([Close], "hh:mm:ss"),
    "Diff", [FormattedDiff],
    "Affected Units", [Affected Unit]
)

RETURN

SELECTCOLUMNS(
    _return,
    [Incident A],
    [B ID],
    [Open],
    [Close],
    [Diff],
    [Affected Units]
)

     

     

    Check out the attached file here for better review and understanding.

     

    Try to change table and column name accordingly. Try implement your own condition based on this.

     

    Hope this helps!!