Forum Discussion

SebastianAlmer's avatar
SebastianAlmer
Frequent Visitor
5 years ago
Solved

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 o...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    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:

    2) 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:

    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:

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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.

 

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

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.