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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi Anonymous ,

 

Please new a calculated column first:

TotalGap = CALCULATE(SUM('Table'[Gap]), 'Table'[1 or 0]=0, ALLEXCEPT('Table','Table'[Instance]))

Then create a new table:

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 ( MIN ( 'Table'[Close] ), ALLEXCEPT ( 'Table', 'Table'[Instance] ) ),
            "Gap", BLANK ()
        ),
        "Duration", [Close] - [Open]
    )
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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Anonymous I am afraid this is partly correct, result of "Close" column in Instance 1 one should be the last value of "Close" 0:30:43. You can refer my image from above.
Also can you add how I can get Gap calculated column basically difference between current row of "Open" and previous row of "Close".

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      a