add yes/no column if row meets certain criteria

[siddp]

Hello!

I'm new to PBI and DAX and am struggling with this problem. Please see attached .pbox with sample data

Breaks.pbix

I have to tables, Break Schedule and Down Time.

I would like to add a column to Down Time that displays Yes when a Down Time is likely a break.

Here is the logic that I'm trying to implement to determine if a down time is a break:

1. The down time duration must be longer than the break duration

2. The down time has to start within 1 hour of the scheduled break start time.

Using this logic, I would expect Row 2 and Row 5 to be marked as a break, as seen below:

I have a vague idea of what to do, but don't have a clue how to go about it in PBI/DAX.

Any guidance on this will be greatly appreciated!!!

Thanks,

Sidd

[v-jiascu-msft · Accepted solution]

Hi Sidd,

You can try a calculated column like this.

Column =
VAR breaktime1 =
    CALCULATE (
        VALUES ( 'Break Schedule'[Break Start] ),
        'Break Schedule'[Index] = 1
    )
VAR breakduration1 =
    CALCULATE ( VALUES ( 'Break Schedule'[Duration] ), 'Break Schedule'[Index] = 1 )
VAR breaktime2 =
    CALCULATE (
        VALUES ( 'Break Schedule'[Break Start] ),
        'Break Schedule'[Index] = 2
    )
VAR breakduration2 =
    CALCULATE ( VALUES ( 'Break Schedule'[Duration] ), 'Break Schedule'[Index] = 2 )
VAR breaktime3 =
    CALCULATE (
        VALUES ( 'Break Schedule'[Break Start] ),
        'Break Schedule'[Index] = 3
    )
VAR breakduration3 =
    CALCULATE ( VALUES ( 'Break Schedule'[Duration] ), 'Break Schedule'[Index] = 3 )
RETURN
    IF (
        'Down Time'[Duration] <= MIN ( 'Break Schedule'[Duration] ),
        "No",
        IF (
            ABS ( DATEDIFF ( TIMEVALUE ( [Time] ), breaktime1, MINUTE ) ) <= 60
                && [Duration] > breakduration1,
            "Yes",
            IF (
                ABS ( DATEDIFF ( TIMEVALUE ( [Time] ), breaktime2, MINUTE ) ) <= 60
                    && [Duration] > breakduration2,
                "Yes",
                IF (
                    ABS ( DATEDIFF ( TIMEVALUE ( [Time] ), breaktime3, MINUTE ) ) <= 60
                        && [Duration] > breakduration3,
                    "Yes",
                    "No"
                )
            )
        )
    )

I added a test row to verify the formula above.

Best Regards,

Dale

[siddp]

Thanks Dale! Your solution was taught me how to use PowerBI a little better. Cheers!