Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Selectively filtering events based upon overlapping date ranges and nested conditions

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....   Essentia...
Need Help
Tips and Tricks
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi Anonymous,

     

    Sorry for the delay response!:smileyhappy:

     

    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

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

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.:smileyhappy:

 

Regards

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi Anonymous,

     

    Sorry for the delay response!:smileyhappy:

     

    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