Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
SebastianAlmer
Frequent Visitor

identify overlapping entries

Dear all,

 

this might not be the best task for PowerBI but we have made so much great reports in our organization and would prevent to have this report in another tool.

We want to identify in our hour bookings overlapping entries and I have really no idea how to do that 🤣🙈
Any help is highly appreciated.

We have a table with time entries:

employeedatestart_hourstart_minuteend_hourend_minuteKSTTask
10000501.03.202165516303700110430
10000501.03.202116301730910430
10000502.03.202165518303700110430
10000502.03.20211730180910430
10000503.03.202165516253700110430
10000503.03.202116251725910430

 

I want to identfy the blue marked lines, cause start hour+min of the second red line is overlapping with the end hour+min (red).

Any idea how to solve this?

thanks in advance

regards
Sebastian

1 ACCEPTED SOLUTION

@SebastianAlmer 

 

If you wish to use Direct Query, you can (NB, I've added another overlapping row to date 2/03/2021 to check if all overlapping rows are returned) :

1) Create the DateTime columns in the table using calculated columns with:

 

 

StartTime = TableDQ[date] & " " & TableDQ[start_hour] & ":" & TableDQ[start_minute]

 

 

And the equivalent for the EndTime column.  Set the Data type to Date/time:

Calc column DQ.JPG2) Create a measure to establish the row order by date/employee/task:

 

 

Sum StartTime = SUM(TableDQ[StartTime])
Order by date =
RANKX (
    FILTER (
        ALLEXCEPT ( TableDQ, TableDQ[employee], TableDQ[date], TableDQ[Task] ),
        NOT ( ISBLANK ( [Sum StartTime] ) )
    ),
    [Sum StartTime],
    ,
    ASC
)

 

This gives you the following table visual:

Full DQ Table.JPG

3) Next create measures to establish the StartDateTime and EndDateTime for the comparison:

 

StartDateTime =
VAR starttime =
    MAX ( TableDQ[StartTime] )
RETURN
    CALCULATE (
        starttime,
        ALLEXCEPT ( 'TableDQ', 'TableDQ'[date], 'TableDQ'[employee], 'TableDQ'[Task] )
    )
EndDateTime =
VAR PrevPeriod = [Order by date] - 1
RETURN
    CALCULATE (
        MAX ( TableDQ[EndTime] ),
        FILTER (
            ALLEXCEPT ( TableDQ, 'TableDQ'[date], 'TableDQ'[employee], TableDQ[Task] ),
            [Order by date] = PrevPeriod
        )
    )

 

4) and finally the measure to identify the rows which overlap:

 

Indentify overlapping DQ =
VAR SelPeriod =
    CALCULATE (
        [Order by date],
        FILTER ( TableDQ, NOT ( ISBLANK ( [EndDateTime] ) ) )
    )
VAR _table =
    CALCULATETABLE (
        VALUES ( TableDQ[date] ),
        FILTER (
            TableDQ,
            [Order by date] >= SelPeriod - 1
                && [Order by date] <= SelPeriod
        )
    )
VAR _date =
    CALCULATETABLE (
        VALUES ( 'TableDQ'[date] ),
        FILTER ( ALL ( TableDQ ), [StartDateTime] < [EndDateTime] )
    )
RETURN
    COUNTROWS ( INTERSECT ( _table, _date ) )

 

And you will get this:

Result DQ.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

9 REPLIES 9
Anonymous
Not applicable

Hello,

I used your code with some modifications based on my specifications and keep coming up with the same error, even though I am in import mode.

James_Lincoln_1-1689188134300.png

 

My data table is structured like this. I created the StartTime and EndTime as a column rather than a measure..

James_Lincoln_0-1689187746259.png

Here is my modified code. 

2) 

##

Sum StartTime = SUM(Timecard_Dimensions[StartTime])

##

and 
##
Order by date =
RANKX (
FILTER (
ALL(Timecard_Dimensions[Clock_In_Time], Timecard_Dimensions[Clock_In_Time], Timecard_Dimensions[timecard_id], Timecard_Dimensions[timecard_Fact.Date_of_Work]),
NOT ( ISBLANK ( [Sum StartTime] ) )
),
[Sum StartTime],
,
ASC
)

##

I changed the allExcept to All as I have a lot of dimensions in the table that are not in the visual, which is possible a issue.

 

 

3) 

##

StartDateTime =
VAR starttime =
MAX ( Timecard_dimensions[StartTime] )
RETURN
CALCULATE (
starttime,
ALL(Timecard_Dimensions[Clock_In_Time], Timecard_Dimensions[Clock_In_Time], Timecard_Dimensions[timecard_id], Timecard_Dimensions[timecard_Fact.Date_of_Work])
)

##

##

EndDateTime =
VAR PrevPeriod = [Order by date] - 1
RETURN
CALCULATE (
MAX ( Timecard_Dimensions[Clock_Out_Time] ),
FILTER (
ALL(Timecard_Dimensions[Clock_In_Time], Timecard_Dimensions[Clock_Out_Time], Timecard_Dimensions[timecard_id], Timecard_Dimensions[timecard_Fact.Date_of_Work]),
[Order by date] = PrevPeriod
)
)

##

 4)

##

Indentify overlapping Measure =
VAR SelPeriod =
CALCULATE (
[Order by date],
FILTER ( Timecard_Dimensions, NOT ( ISBLANK ( [Clock_Out_Time] ) ) )
)
VAR _table =
CALCULATETABLE (
VALUES ( Timecard_Dimensions[Timecard_Fact.Date_of_Work] ),
FILTER (
Timecard_Dimensions,
[Order by date] >= SelPeriod - 1
&& [Order by date] <= SelPeriod
)
)
VAR _date =
CALCULATETABLE (
VALUES ( 'Timecard_Dimensions'[Timecard_Fact.Date_of_Work] ),
FILTER ( ALL ( timecard_Dimensions ), [Clock_In_Time] <= [Clock_Out_Time] )
)
RETURN
COUNTROWS ( INTERSECT ( _table, _date ) )

##

 

I have tried any all of these to the visuals going down through the steps. Order by Date works in the visual without the error as well as StartDateTime. EndDateTime runs the error as well as Indentify overlapping measure.

 

If anyone has any tips on how to solve this problem, that would be greatly appreciated. Thank you in advance.

 

v-luwang-msft
Community Support
Community Support

Hi  @SebastianAlmer  ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution to help others find it.
 
Best Regards
Lucien
SebastianAlmer
Frequent Visitor

I've switched to import for testing:
@jaideepnema 

I have an index that I can use. But it's not ordered by anything. Your index needs to be ordered I assume? Like in the solution of @PaulDBrown 

@PaulDBrown I've tested your solution. It seems, that it only works, if there are two entries per day, right? How could I do this not knowing how many entries per day?

really appreciate your help🙏

@SebastianAlmer 

 

If you wish to use Direct Query, you can (NB, I've added another overlapping row to date 2/03/2021 to check if all overlapping rows are returned) :

1) Create the DateTime columns in the table using calculated columns with:

 

 

StartTime = TableDQ[date] & " " & TableDQ[start_hour] & ":" & TableDQ[start_minute]

 

 

And the equivalent for the EndTime column.  Set the Data type to Date/time:

Calc column DQ.JPG2) Create a measure to establish the row order by date/employee/task:

 

 

Sum StartTime = SUM(TableDQ[StartTime])
Order by date =
RANKX (
    FILTER (
        ALLEXCEPT ( TableDQ, TableDQ[employee], TableDQ[date], TableDQ[Task] ),
        NOT ( ISBLANK ( [Sum StartTime] ) )
    ),
    [Sum StartTime],
    ,
    ASC
)

 

This gives you the following table visual:

Full DQ Table.JPG

3) Next create measures to establish the StartDateTime and EndDateTime for the comparison:

 

StartDateTime =
VAR starttime =
    MAX ( TableDQ[StartTime] )
RETURN
    CALCULATE (
        starttime,
        ALLEXCEPT ( 'TableDQ', 'TableDQ'[date], 'TableDQ'[employee], 'TableDQ'[Task] )
    )
EndDateTime =
VAR PrevPeriod = [Order by date] - 1
RETURN
    CALCULATE (
        MAX ( TableDQ[EndTime] ),
        FILTER (
            ALLEXCEPT ( TableDQ, 'TableDQ'[date], 'TableDQ'[employee], TableDQ[Task] ),
            [Order by date] = PrevPeriod
        )
    )

 

4) and finally the measure to identify the rows which overlap:

 

Indentify overlapping DQ =
VAR SelPeriod =
    CALCULATE (
        [Order by date],
        FILTER ( TableDQ, NOT ( ISBLANK ( [EndDateTime] ) ) )
    )
VAR _table =
    CALCULATETABLE (
        VALUES ( TableDQ[date] ),
        FILTER (
            TableDQ,
            [Order by date] >= SelPeriod - 1
                && [Order by date] <= SelPeriod
        )
    )
VAR _date =
    CALCULATETABLE (
        VALUES ( 'TableDQ'[date] ),
        FILTER ( ALL ( TableDQ ), [StartDateTime] < [EndDateTime] )
    )
RETURN
    COUNTROWS ( INTERSECT ( _table, _date ) )

 

And you will get this:

Result DQ.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

Can you share the .pbix file of this solution on the forum? Thanks. 

Hi @SebastianAlmer ,

Yes the index needs to be in a sorted order ...preferably in ascending order.....

SebastianAlmer
Frequent Visitor

@PaulDBrown @jaideepnema 
Really appreciate your solutions there. I might have missed the information that im working in DirectQuery mode. oops...

Do you see any easy solution with direct query? I can't create this calculated column with the "CALCULATE" function and with a measure I'm not able to user "EARLIER". Any idea? if not, then I might need to switch to imported...

thanks in advance

jaideepnema
Solution Sage
Solution Sage

Hi @SebastianAlmer 
Add a index column to your table and then Create two calculated column like this:

 

Time Flag = 

var previousendhour=CALCULATE(MAX('Table'[end_hour]),FILTER(ALLEXCEPT('Table','Table'[date]),'Table'[Index]=EARLIER('Table'[Index])-1))

var previousendminute=CALCULATE(MAX('Table'[end_minute]),FILTER(ALLEXCEPT('Table','Table'[date]),'Table'[Index]=EARLIER('Table'[Index])-1))

var previousendtime= (previousendhour*100)+previousendminute

var starttime=('Table'[start_hour]*100)+'Table'[start_minute]

var timeflag=IF(starttime<previousendtime,1,0)

return timeflag

 

Next Time Flag = CALCULATE(MAX('Table'[Time Flag]),FILTER(ALLEXCEPT('Table','Table'[date]),'Table'[Index]=EARLIER('Table'[Index])+1))
 
and then create a measure like this
Time Filter = IF(SUM('Table'[Time Flag])=1 || sum('Table'[Next Time Flag])=1,1,0)
 
Apply the above measure as a visual level filter to get the desired result:
jaideepnema_0-1614924420410.png

 

 
In case you need the file here is the link:
 

Please accept this as a solution if your question has been answered !!

Appreciate a Kudos 😀

 
 
PaulDBrown
Community Champion
Community Champion

@SebastianAlmer 
I'm not too sure if this will work flawlessly but...

1) Create a new columns in Power Query to return the Date StartTime and Date EndTimes per row.

2) Add a new calculated column to rank the rows by employee, date and task in ascending order based on the StartTime value:

Calc Column.JPG

 3) Create a measure to check if a start time overlaps an end time:

 

Indentify overlapping =
VAR EndDateTime =
    CALCULATE (
        MAX ( 'Table'[End DateTime] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[date], 'Table'[employee], 'Table'[Task] ),
            'Table'[Order] = 1
        )
    )
VAR StartDateTime =
    CALCULATE (
        MAX ( 'Table'[Start DateTime] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[date], 'Table'[employee], 'Table'[Task] ),
            'Table'[Order] = 2
        )
    )
RETURN
    IF ( StartDateTime < EndDateTime, 1 )

 

 And you get this:

result.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.