Merge based on multiple conditions

[Anonymous]

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. 

[shafiz_p · Accepted solution]

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!!

[Anonymous]

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

[rajendraongole1]

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]

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