Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Merge data by using conditions and filter

Hello, community If someone can help me with this, it would be of great help. Thank you. This is my sample(raw) data: 1 or 0 Instance  Sub Instance Open Close Duration Gap 0 Insta...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

     

    I edited the code.

    Table2 = 
VAR __table_1 =
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER ( 'Table', 'Table'[1 or 0] = 0 && 'Table'[TotalGap] <= TIME ( 0, 1, 0 ) ),
            'Table'[1 or 0],
            'Table'[Instance],
            "Sub Instance",
                CALCULATE (
                    MIN ( 'Table'[Sub Instance] ),
                    ALLEXCEPT ( 'Table', 'Table'[Instance] )
                ),
            "Open", CALCULATE ( MIN ( 'Table'[Open] ), ALLEXCEPT ( 'Table', 'Table'[Instance] ) ),
            "Close", CALCULATE ( MAX ( 'Table'[Close] ), ALLEXCEPT ( 'Table', 'Table'[Instance] ) ),
            "Gap", BLANK ()
        ),
        "Duration", [Close] - [Open] -  CALCULATE(SUM('Table'[Gap]), 'Table'[1 or 0]=0, ALLEXCEPT('Table','Table'[Instance]))
    )
VAR __table_0 =
SELECTCOLUMNS(
    FILTER ( 'Table', 'Table'[1 or 0] = 0 && 'Table'[TotalGap] >= TIME ( 0, 1, 0 ) ),
    "1 or 0", [1 or 0],
    "Instance", [Instance],
    "Sub Instance", [Sub Instance],
    "Open",[Open],
    "Close",[Close],
    "Gap",[Gap],
    "Duration",[Duration]
)
RETURN
    UNION(__table_1,__table_0)

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

Anonymous 

Create a new column to calculate the total gap for each instance. You can use DAX (Data Analysis Expressions) to achieve this.

DAX
TotalGap =
VAR CurrentInstance = 'Table'[Instance]
RETURN
SUMX(
FILTER('Table', 'Table'[Instance] = CurrentInstance),
'Table'[Gap]
)

 

Create a new table or modify the existing table to merge rows based on the condition that the total gap is less than a minute.

DAX
MergedTable =
ADDCOLUMNS(
SUMMARIZE(
'Table',
'Table'[Instance],
'Table'[Open],
'Table'[Close],
'Table'[Duration],
'Table'[Gap]
),
"MergedDuration",
IF(
[TotalGap] < TIME(0, 1, 0),
SUMX(
FILTER('Table', 'Table'[Instance] = EARLIER('Table'[Instance])),
'Table'[Duration]
),
'Table'[Duration]
)
)