Selectively filtering events based upon overlapping date ranges and nested conditions

[Anonymous]

Let me start by saying I have found a messy, multi-step solution of IF, CALCULATE and FILTER for this but it is an ugly, expensive process and I feel like there must be a simpler way....

Essentially I have data on a series of industry events and comes in from an external source (let's call it ExternalData) with OutageID, UnitID, StartDate, and EndDate. However, I also created a PowerApp that brings in data in an identical format but with different content and is not directly replacing the data in ExternalData (let's call it PowerAppData). In addition, both tables have many other captured values, and PowerAppData does not have any values for OutageID. However, there are some cases where the data from PowerAppData should be used to replace entries in ExternalData. Specifically, this should occur when the UnitID matches and when the range of dates from StartDate to EndDate has any overlap.

I have used a variation of the method described in this link to turn both tables into schedule tables with an entry for every date that occurs. For example, the following format:

I am looking for the most efficient method of replacing/ combining values so that if I append the tables, I don't end up with any rows with the same UnitID and same Date, but must also remove any corresponding dates from the same OutageID from the ExternalData table. I envisioned some sort of key or index that is filtered in the event that there is an overlap, rather than the series of nested CALCULATE and FILTER steps that I ended up using with duplicate tables and queries.

Happy to elaborate further if that wasn't clear....

Thanks!

[v-ljerr-msft]

Hi @Anonymous,

---

---

However, there are some cases where the data from PowerAppData should be used to replace entries in ExternalData. Specifically, this should occur when the UnitID matches and when the range of dates from StartDate to EndDate has any overlap.

For each record in ExternalData, how many records that meet the condition above could there be in PowerAppData?

Could you post some sample data for ExternalData and PowerAppData with your final expected result? It's better that you could also share a sample pbix file. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Regards

[Anonymous]

Ideally, for each record in ExternalData, there could be multiple records that replace it from PowerAppData, and there could be multiple records in ExternalData that could be replaced by a single record in PowerAppData. This is why I had attempted to interpolate the set of days for every date between the start and end date and look for overlaps between the two. However, I'd be happy if I can start by having single records replaced if there is any overlap.

I created a sample file accessible here that has just the essential tables (the actual model is large and pulls in millions of rows into the ExternalData table). I created some "tricky" example scenarios in the data. For instance, you'll note that:

(1) For UnitID 100, the ExternalData shows an event from 1/1/17 to 1/3/17 as well as an event from 1/28/17 to 3/15/17. The PowerAppData shows an event for UnitID 100 which lasts from 1/2/17 to 1/4/17. Apparently the user knows of an event, but it is shifted by a day. I believe the PowerApp more than I believe the External source, so I'd like to replace the relevant ExternalData while keeping the other event for UnitID 100.

(2) For UnitID 101, the PowerAppData shows an event that is non overlapping with the one for UnitID 101 within ExternalData. I would like both to appear.

(3) For UnitID 103, the PowerAppData shows an event from 1/1/17 to 1/9/17. The ExternalData shows two events for UnitID 103 that overlap with this time frame, and I'd like to replace both.

 Thanks for any help! I've built many dashboards and tools, but this one has stumped me more than I'd like to admit.

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

Sorry for the delay response!

I have went through the sample pbix file shared above. May I ask how do you want to show these data on the report?

I am just wondering whether using the Merge Queries option in Query Editor to merge these two tables into a single one could meet your requirement in this scenario.

Following is the merged table.

Then we can add a calculate column in the Merged table to indicate if the ExternalData.StartDate and ExternalData.EndDate should be replaced by PowerAppData.StartDate and PowerAppData.EndDate.

Flag = 
IF (
    ISBLANK ( MergeTable[PowerAppData.StartDate] )
        && ISBLANK ( MergeTable[PowerAppData.EndDate] ),
    BLANK (),
    IF (
        ( MergeTable[PowerAppData.StartDate] >= MergeTable[ExternalData.StartDate]
            && MergeTable[PowerAppData.StartDate] <= MergeTable[ExternalData.EndDate] )
            || ( MergeTable[PowerAppData.EndDate] >= MergeTable[ExternalData.StartDate]
            && MergeTable[PowerAppData.EndDate] <= MergeTable[ExternalData.EndDate] ),
        "Replace",
        "Keep"
    )
)

Last, show the data with a Slicer of the "Flag" column on the report.

Here is the modified sample pbix file for your reference.

Regards