Solution required for finding 'data gap' in table and adding a new row showing the correct status

Hi DontLikeMondays 

Thanks for Posting in Microsoft forum Community. 

 

As per the Query you mentioned for Solution required for finding 'data gap' in table and adding a new row showing the correct status. The provided DAX query is designed to find gaps in a table's data where the StartTime of a new row does not immediately follow the EndTime of the previous row for the same PC Name. It then creates new rows to fill those gaps, assigning them the status "Ideal". Here's a breakdown of how the code works: 

 

Step1: First it Creates a new intermediate table (GapRows) that includes additional calculated columns: 
VAR GapRows = 

    ADDCOLUMNS( 

        'Data',  // Table name is 'Data' 

        "NextStartTime", 

        CALCULATE( 

            MIN('Data'[StartTime]),  // Get the next StartTime for the same PC Name 

            FILTER( 

                'Data',  // Filtering to get the next row 

                'Data'[PC Name] = EARLIER('Data'[PC Name]) && 

                'Data'[StartTime] > EARLIER('Data'[StartTime])  // Ensure we only look ahead 

            ) 

        ), 

        "CurrentEndTime", 'Data'[EndTime]  // Rename existing EndTime to avoid conflict 

    ) 

 
Step2: Identifies rows where there is a gap between the CurrentEndTime of a row and the NextStartTime of the next row for the same PC Name: 

VAR IdealRows = 

    SELECTCOLUMNS( 

        FILTER( 

            GapRows, 

            NOT ISBLANK([NextStartTime]) && [CurrentEndTime] < [NextStartTime]  // Detect gaps 

        ), 

        "PC Name", [PC Name], 

        "Status", "Ideal",  // Add status as "Ideal" for the gap rows 

        "StartTime", [CurrentEndTime],  // Start time of the "Ideal" row is the current EndTime 

        "EndTime", [NextStartTime]  // End time of the "Ideal" row is the next StartTime 

    ) 

 

 
Step3: Combine Original and Gap Rows: 

                    RETURN 

                           UNION( 

                            'Data',  // Combine original data 

                                IdealRows  // Combine with the newly generated Ideal rows 

                                     ) 

Using the above code, I obtained the expected output. I opted to use "Ideal" instead of "Unavailable" to enhance clarity and understanding. 

 

By utilizing conditional formatting, I was able to highlight the desired results. 

 

 Using the Background Color feature, I set a rule to get the desired results. 

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly. 

Thank you.