range match of event dates within a start and end conditions

[aherabit]

Hi Folks.

Trying a condition match for event execution timestamp against maintenance start and end periods.  The query match only working for exact matches of hh:mm.

I have tried many methods, measure & calculated columns, and used ChatGPT for improvements...but hitting a wall...I appreciate any human brain help.

Here is sample of the maintenance window

 disruption_start_date_UTC,DateTimeKey_Disruption_start,disruption_end_date_UTC,DateTimeKey_Disruption_end
2025-04-10 20:00:00,202504102000,2025-04-11 00:00:00,202504110000

here is the output:

executionEndDate,IsExecutionWithinDisruption,DateKey
2025-04-10 19:56:25,No,202504101956
2025-04-10 19:56:36,No,202504101956
2025-04-10 19:57:53,No,202504101957
2025-04-10 19:58:45,No,202504101958
2025-04-10 19:59:43,No,202504101959
2025-04-10 20:00:36,Yes,202504102000
2025-04-10 20:00:59,Yes,202504102000
2025-04-10 20:05:31,No,202504102005
2025-04-10 20:05:32,No,202504102005
2025-04-10 20:06:08,No,202504102006
2025-04-10 20:07:13,No,202504102007
2025-04-10 20:08:48,No,202504102008
2025-04-10 20:09:33,No,202504102009
2025-04-10 20:12:27,No,202504102012
2025-04-10 20:15:15,No,202504102015
2025-04-10 20:15:50,No,202504102015
2025-04-10 21:03:24,No,202504102103
2025-04-10 22:49:20,No,202504102249
2025-04-10 22:52:00,No,202504102252
2025-04-10 23:00:03,No,202504102300
2025-04-10 23:00:14,No,202504102300
2025-04-10 23:00:45,No,202504102300
2025-04-10 23:28:44,No,202504102328

<< produced partial match of hh:mm only >> IsExecutionWithinDisruption = VAR ExecDateTime = MAX('KPI_TRAFFIC_filtered-label-n-executed'[executionEndDate]) RETURN IF( NOT ISBLANK(ExecDateTime) && COUNTROWS( FILTER( 'public disruption_request', ExecDateTime >= 'public disruption_request'[disruption_start_date_UTC] && ExecDateTime <= 'public disruption_request'[disruption_end_date_UTC] ) ) > 0, "Yes", "No" )

<< this code did not work >> IsExecutionWithinDisruption_2 = VAR ExecDateTime = 'KPI_TRAFFIC_filtered-label-n-executed'[executionEndDate] RETURN IF ( CALCULATE ( COUNTROWS ( FILTER ( 'public disruption_request', ExecDateTime >= 'public disruption_request'[disruption_start_date_UTC] && ExecDateTime <= 'public disruption_request'[disruption_end_date_UTC] ) ) ) > 0, "Yes", "No" )

[aherabit]

Thank you for your continued interest in this issue.

Tracking ID #2505210010001785.
They've escalated the issue to their DAX experts, and we’ll share further updates as they become available.

[aherabit]

Hi Sahasra...  I was away...and the issue has not been resolved yet.

[v-sgandrathi]

Hi @aherabit,

I just wanted to check in regarding a support ticket. Could you please confirm whether you’ve already raised a support ticket for this issue?

If you have, this message serves as an update related to that ticket. Kindly review the details and confirm so we can ensure everything is moving in the right direction.

If you haven’t yet raised a ticket, please let me know so we can assist you accordingly.

Looking forward to your confirmation.

Thank you.

[v-sgandrathi]

Hi @aherabit,

We are following up once again regarding your query. Could you please confirm if the issue has been resolved through the support ticket with Microsoft?

If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. If we don’t hear back, we’ll go ahead and close this thread.

Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you for your understanding and participation.

[v-sgandrathi · Accepted solution]

Hi @aherabit,
Thank you once again for your persistence and detailed feedback throughout this process. We truly appreciate the time and effort you've dedicated to validating each solution.

After exploring multiple solutions from various trusted sources, we've encountered persistent challenges in achieving the expected outcome. Given these continued roadblocks, it appears that the issue may require a more in-depth analysis and potentially a tailored approach specific to your environment and system configuration.

At this stage, to ensure a faster and more definitive resolution, we strongly recommend raising a formal support ticket with Microsoft. Their technical team can investigate with deeper access to diagnostics and provide a tailored fix if needed.

To raise a support ticket for Fabric and Power BI, kindly follow the steps outlined in the following guide:
How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

Thank you.

[v-sgandrathi]

Hi @aherabit,

Could you please confirm if the issue has been resolved after raising a support case? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.

Thank you for your understanding and assistance.

[aherabit]

Thank you, Sahasra...We are pursuing the MS support path.

[v-sgandrathi]

Hi @aherabit,

Thank you for providing the update. I sincerely hope that the issue will be resolved promptly and that you will be able to find an effective solution. Your efforts are much appreciated, and I look forward to hearing more about the progress.

Continue using Microsoft Fabric Comunity Forum.

[v-sgandrathi]

Hi @aherabit,

Thank you for reaching out to the community.

Based on your explanation and sample data, your current logic only evaluates matches at the hour-minute (hh:mm) level due to reliance on DateTimeKey or similar truncated values. As a result, the condition is only returning "Yes" when the executionEndDate exactly matches the start of the disruption window at 20:00.
To correctly flag whether each execution timestamp falls within a disruption window including full precision down to seconds you'll want to ensure that you're comparing full datetime values, not formatted keys or rounded timestamps.

Please try the following DAX measure, which evaluates whether the executionEndDate falls between any matching disruption_start_date_UTC and disruption_end_date_UTC entries:


IsExecutionWithinDisruption =
VAR ExecDateTime = 'KPI_TRAFFIC_filtered-label-n-executed'[executionEndDate]
RETURN
IF (
CALCULATE (
COUNTROWS (
FILTER (
'public disruption_request',
ExecDateTime >= 'public disruption_request'[disruption_start_date_UTC] &&
ExecDateTime <= 'public disruption_request'[disruption_end_date_UTC]
)
)
) > 0,
"Yes",
"No"
)

Ensure that 'executionEndDate' and the disruption timestamps are stored as full datetime fields and not rounded or converted to text or integers like DateTimeKey. Otherwise, the match will only work at hh:mm granularity.


If your model is large and performance is a concern, consider using a calculated column with similar logic. You may also review whether multiple overlapping disruption windows exist and clarify how you'd like them prioritized if applicable.

Please Accept as solution if this meets your needs and a Kudos would be appreciated.

[aherabit]

Thanks, V.

I have tried your suggestion, and the codition match fails completely.  

here is the outcome:
executionEndDate,IsExecutionWithinDisruption_2,DateKey
2025-04-10 19:56:25,No,202504101956
2025-04-10 19:56:36,No,202504101956
2025-04-10 19:57:53,No,202504101957
2025-04-10 19:58:45,No,202504101958
2025-04-10 19:59:43,No,202504101959
2025-04-10 19:57:53,No,202504101957
2025-04-10 19:58:45,No,202504101958
2025-04-10 19:59:43,No,202504101959
2025-04-10 20:00:36,No,202504102000
2025-04-10 20:00:59,No,202504102000
2025-04-10 20:05:31,No,202504102005
2025-04-10 20:05:32,No,202504102005
2025-04-10 20:06:08,No,202504102006
2025-04-10 20:07:13,No,202504102007
2025-04-10 20:08:48,No,202504102008
2025-04-10 20:09:33,No,202504102009
2025-04-10 20:12:27,No,202504102012
2025-04-10 20:15:15,No,202504102015
2025-04-10 20:15:50,No,202504102015
2025-04-10 21:03:24,No,202504102103
2025-04-10 22:49:20,No,202504102249
2025-04-10 22:52:00,No,202504102252
2025-04-10 23:00:03,No,202504102300
2025-04-10 23:00:14,No,202504102300
2025-04-10 23:00:45,No,202504102300
2025-04-10 23:28:44,No,202504102328

[v-sgandrathi]

Hi @aherabit,

Thank you for reaching out in Microsoft Community Forum.

The DAX logic isn't correctly matching the executionEndDate with the disruption time window, likely due to precision issues (milliseconds, rounding) or incorrect time zone handling.

Please follow below steps to resolve the issue;

To avoid precision issues, round the executionEndDate and disruption window timestamps to the nearest minute for a more straightforward comparison.

IsExecutionWithinDisruption_Rounded =
VAR ExecDateTime = 'KPI_TRAFFIC_filtered-label-n-executed'[executionEndDate]
VAR RoundedExecDateTime = 
    DATETIME(YEAR(ExecDateTime), MONTH(ExecDateTime), DAY(ExecDateTime), HOUR(ExecDateTime), MINUTE(ExecDateTime), 0)
RETURN
IF (
    NOT ISBLANK(RoundedExecDateTime) &&
    COUNTROWS (
        FILTER (
            'public disruption_request',
            RoundedExecDateTime >= 'public disruption_request'[disruption_start_date_UTC] &&
            RoundedExecDateTime <= 'public disruption_request'[disruption_end_date_UTC]
        )
    ) > 0,
    "Yes",
    "No"
)

Add a debugging measure to verify how many rows match the condition:

DisruptionMatchCount = 
VAR ExecDateTime = 'KPI_TRAFFIC_filtered-label-n-executed'[executionEndDate]
RETURN
COUNTROWS(
    FILTER(
        'public disruption_request',
        ExecDateTime >= 'public disruption_request'[disruption_start_date_UTC] &&
        ExecDateTime <= 'public disruption_request'[disruption_end_date_UTC]
    )
)

Double-check that the executionEndDate and disruption dates are in the same time zone.

Make sure all datetime fields are consistently stored as DateTime (not text or integers).

If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos'. if it was helpful. help other members find it more easily.

Regards,
Sahasra.

[aherabit]

Thanks again.
It, incorrectly, matched 100% and the condition check failed for all.

I had to revise your query to the following:

IsExecutionWithinDisruption_Rounded =

VAR ExecDateTime = 'KPI_TRAFFIC_filtered-label-n-executed'[executionEndDate]

VAR RoundedExecDateTime =

    DATE(YEAR(ExecDateTime), MONTH(ExecDateTime), DAY(ExecDateTime)) +

    TIME(HOUR(ExecDateTime), MINUTE(ExecDateTime), 0)

RETURN

IF (

    NOT ISBLANK(RoundedExecDateTime) &&

    COUNTROWS (

        FILTER (

            'public disruption_request',

            RoundedExecDateTime >= 'public disruption_request'[disruption_start_date_UTC] &&

            RoundedExecDateTime <= 'public disruption_request'[disruption_end_date_UTC]

        )

    ) > 0,

    "Yes",

    "No"

)

Here is the outcome:
executionEndDate,IsExecutionWithinDisruption_Rounded,DateKey
2025-04-10 19:56:25,Yes,202504101956
2025-04-10 19:56:36,Yes,202504101956
2025-04-10 19:57:53,Yes,202504101957
2025-04-10 19:58:45,Yes,202504101958
2025-04-10 19:59:43,Yes,202504101959
2025-04-10 19:57:53,Yes,202504101957
2025-04-10 19:58:45,Yes,202504101958
2025-04-10 19:59:43,Yes,202504101959
2025-04-10 20:00:36,Yes,202504102000
2025-04-10 20:00:59,Yes,202504102000
2025-04-10 20:05:31,Yes,202504102005
2025-04-10 20:05:32,Yes,202504102005
2025-04-10 20:06:08,Yes,202504102006
2025-04-10 20:07:13,Yes,202504102007
2025-04-10 20:08:48,Yes,202504102008
2025-04-10 20:09:33,Yes,202504102009
2025-04-10 20:12:27,Yes,202504102012
2025-04-10 20:15:15,Yes,202504102015
2025-04-10 20:15:50,Yes,202504102015
2025-04-10 21:03:24,Yes,202504102103
2025-04-10 22:49:20,Yes,202504102249
2025-04-10 22:52:00,Yes,202504102252
2025-04-10 23:00:03,Yes,202504102300
2025-04-10 23:00:14,Yes,202504102300
2025-04-10 23:00:45,Yes,202504102300
2025-04-10 23:28:44,Yes,202504102328

[v-sgandrathi]

Hi @aherabit,

Thank you for the update and for sharing your revised logic.

Please follow below steps for required output;

1.The issue is caused by using a measure, which lacks proper row context to evaluate each execution timestamp against disruption windows.

2.Please Replace the measure with a calculated column to evaluate each row individually using full datetime precision.

3. use the below calculated column logic

IsExecutionWithinDisruption_Column =
VAR ExecDateTime = 'KPI_TRAFFIC_filtered-label-n-executed'[executionEndDate]
RETURN
IF (
    COUNTROWS (
        FILTER (
            'public disruption_request',
            ExecDateTime >= 'public disruption_request'[disruption_start_date_UTC] &&
            ExecDateTime <= 'public disruption_request'[disruption_end_date_UTC]
        )
    ) > 0,
    "Yes",
    "No"
)

4.Make sure  both executionEndDate and disruption fields are in the same time zone and stored as proper datetime types.

Please continue using Microsoft community forum.

If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos'. if it was helpful. help other members find it more easily.

Regards,
Sahasra.

[aherabit]

Thank you, Sahasra.

Q: Are you using ChatGPT or Copilot?  I've already explored various solutions using ChatGPT v4.0 and Copilot, but unfortunately, none have provided a valid solution. Given the complexity of the problem, I believe that a direct human intervention is necessary to address it effectively.

To answer your last suggestion, here is the outcome:
executionEndDate,IsExecutionWithinDisruption_Column,DateKey
2025-04-10 19:56:25,Yes,202504101956
2025-04-10 19:56:36,Yes,202504101956
2025-04-10 19:57:53,Yes,202504101957
2025-04-10 19:58:45,Yes,202504101958
2025-04-10 19:59:43,Yes,202504101959
2025-04-10 19:57:53,Yes,202504101957
2025-04-10 19:58:45,Yes,202504101958
2025-04-10 19:59:43,Yes,202504101959
2025-04-10 20:00:36,Yes,202504102000
2025-04-10 20:00:59,Yes,202504102000
2025-04-10 20:05:31,Yes,202504102005
2025-04-10 20:05:32,Yes,202504102005
2025-04-10 20:06:08,Yes,202504102006
2025-04-10 20:07:13,Yes,202504102007
2025-04-10 20:08:48,Yes,202504102008
2025-04-10 20:09:33,Yes,202504102009
2025-04-10 20:12:27,Yes,202504102012
2025-04-10 20:15:15,Yes,202504102015
2025-04-10 20:15:50,Yes,202504102015
2025-04-10 21:03:24,Yes,202504102103
2025-04-10 22:49:20,Yes,202504102249
2025-04-10 22:52:00,Yes,202504102252
2025-04-10 23:00:03,Yes,202504102300
2025-04-10 23:00:14,Yes,202504102300
2025-04-10 23:00:45,Yes,202504102300
2025-04-10 23:28:44,Yes,202504102328