Find if Patient are forecasted to breach SLA

[eliasayyy]

Hello Everyone,

I have 2 tables

SLA table and Scheduling Table

Sample of SLA Table

PatientID	RoomCode	Exam Code	Breaching?	Appointment Duration(mins)
1	abMRI	ab	1	20
1	abMRI	ac	1	20
2	hgCT	tc	0	55
3	tyCT	tc	0	30
4	poMRI	ab	1	25
5	poMRI	ab	1	40

here is sample of scheduling Table

Date	Schedule Time	Appoitnment Ends	RoomCode	Gap In Minutes
17/12/25	7:45 AM	08:30	abMRI	0
17/12/25	08:30	08:45	abMRI	35
17/12/25	09:20	10:00 AM	abMRI	0
17/12/25	7:45 AM	08:00	poMRI	30
17/12/25	08:30	09:15	poMRI	5
17/12/25	09:20	10:00 AM	poMRI	20
17/12/25	10:20	11:00	poMRI	0

My task is to create a card called Patients Breach Forecast

so first we need dsiticnt count of patinets about to breach ignoring dimdate slicer

i have 

Patients To Breach = 
CALCULATE(
    DISTINCTCOUNT(
        'SLA Table'[Patientid + ExamCode]
    ),
    REMOVEFILTERS(dimDate[Date]),
    'SLA Table'[Breached?] = 1
)

'SLA Table'[Patientid + ExamCode] is just concat the 2 columns with "-" so 1-ab

now we have the distinct count of patinet breached category is 1 which is 3

now next step is to see each distinct what their required appoitmnet time would take so for 1-ab, they need 20 mins and for 5-ab, they need 40 mins.

next step is to see if in the next 3 days, we have any available slot gaps from scheduling table so we can fit the patients to be breach in

so today is 15/12/2025, we look starting from 16/12/2025 until 18/12/2025

in my example above, we see for room abMRI, on 17/12/2025, we have a gap of 35 mins available which is more that 1-ab patinet who is needed to visit roomcode abMRI and needs 20 mins, hence now remaining slot is 15 mins for this day and can only accept another patient if he needs 1 or less and so on. 

second patient eamaple 5-ab needs 40 mins, and in next 3 days, we have on 17/12/2025 in room poMRI which in schedule date shows has multiple gaps. but that patient needs 40 mins while max of the gap time in the next 3 days for this room is 30 mins, hence this patient is expected to breach sla and we count him as 1. 

This is very complex and I'm having trouble caclulating how many patients will breach.

so in Summary I need:

1- Find Distinct Count of patients who are breached? = 1
2- Find their respective distinct per [patientid + examcpde] appoitmnet time they need
3- check in scheduling table for any gap in the next 3 days from today so ( Date >= TODAY()+1 && Date <= TODAY() + 3)
4- try to see if patients will fit in the gap, if so, then do ( [gap in minutes] - [Appointment Duration(mins)]) for the time the patient will be filled( example patient needs 20 mins and gap has 30 mins so 30 - 20  =10 remaining gap minutes that can be filled if another patient needs 10 mins or less appointment)) if not then he will be counted as forecasted to breach 
5- show in a card the total amount of patients forecasted to breach

thank you in advanced, feel free to ask any questions

[burakkaragoz · Accepted solution]

Hi @eliasayyy ,

This is indeed a classic "Bin Packing" problem as @Zanqueta  mentioned. Solving this perfectly (fitting small blocks into specific small holes iteratively) is extremely difficult in standard DAX measures because DAX calculates everything at once, not step-by-step.

However, we can create a very close Forecast using a "Running Total" logic in DAX.

The Logic: Instead of trying to fit "Patient A into Gap 1", we assume a First-Come-First-Served model:

1. We calculate the Total Gap Capacity available per Room for the next 3 days.

2. We calculate the Running Total of Demand (Cumulative Duration) for the patients waiting for that room.

3. If a patient's cumulative demand exceeds the room's total capacity (or if their individual duration is larger than the single biggest gap), they are flagged as "Breaching".

Here is the DAX solution to achieve this:

Forecast Breach Count = 
VAR vStartData = TODAY() + 1
VAR vEndDate = TODAY() + 3

-- 1. Get the list of patients who are already flagged as Breaching
VAR _PatientsToBreach = 
    FILTER(
        ADDCOLUMNS(
            SUMMARIZE(
                'SLA Table',
                'SLA Table'[PatientID],
                'SLA Table'[Exam Code],
                'SLA Table'[RoomCode],
                'SLA Table'[Appointment Duration(mins)]
            ),
            "PatientKey", 'SLA Table'[PatientID] & "-" & 'SLA Table'[Exam Code]
        ),
        -- Assuming 'Breaching?' is a column in SLA Table you want to filter on first
        CALCULATE(SELECTEDVALUE('SLA Table'[Breaching?])) = 1
    )

-- 2. Add Logic to check capacity vs demand
VAR _EnrichedPatients = 
    ADDCOLUMNS(
        _PatientsToBreach,
        
        -- A. Find the MAX single gap available (to catch patients who are just too big for any slot)
        "MaxGapSize", CALCULATE(
            MAX('Scheduling Table'[Gap In Minutes]),
            'Scheduling Table'[Date] >= vStartData,
            'Scheduling Table'[Date] <= vEndDate,
            'Scheduling Table'[RoomCode] = EARLIER([RoomCode])
        ),

        -- B. Find TOTAL capacity (Sum of all gaps)
        "TotalRoomCapacity", CALCULATE(
            SUM('Scheduling Table'[Gap In Minutes]),
            'Scheduling Table'[Date] >= vStartData,
            'Scheduling Table'[Date] <= vEndDate,
            'Scheduling Table'[RoomCode] = EARLIER([RoomCode])
        ),

        -- C. Calculate Running Total (Cumulative Demand) for this room
        -- This simulates "consuming" the slots one by one.
        "CumulativeDemand", 
            VAR CurrentRoom = [RoomCode]
            VAR CurrentID = [PatientID]
            RETURN
            SUMX(
                FILTER(
                    _PatientsToBreach, 
                    [RoomCode] = CurrentRoom && [PatientID] <= CurrentID
                ),
                [Appointment Duration(mins)]
            )
    )

-- 3. Count who fails the test
VAR _BreachForecast = 
    FILTER(
        _EnrichedPatients,
        -- Condition 1: The patient is bigger than the biggest single gap
        [Appointment Duration(mins)] > [MaxGapSize] || 
        -- Condition 2: The room is full (Cumulative demand exceeds total capacity)
        [CumulativeDemand] > [TotalRoomCapacity]
    )

RETURN
    COUNTROWS(_BreachForecast)

Explanation of the Measure:

1. _PatientsToBreach: Creates a virtual table of your distinct patients who are currently at risk (Breaching? = 1).

2. CumulativeDemand: This is the magic step. It ranks patients (by ID) and adds up their time.

   • Patient 1 (20 mins) -> Total Demand: 20

   • Patient 2 (20 mins) -> Total Demand: 40

3. The Test:

   • If Total Room Capacity is 35 mins:

   • Patient 1 (Demand 20) < 35 -> Safe (Fits).

   • Patient 2 (Demand 40) > 35 -> Breach (No space left).

This avoids the complexity of Power Query while solving the "Consumption" requirement you described in point 4 of your summary.

Note on your data: As @Ashish_Mathur noted, ensure your PatientID and RoomCode relationships are clean, as 5-ab in your text description implies a mismatch with the poMRI room shown in your sample rows. The measure above relies on RoomCode to match demand to supply.

Let me know if this returns the expected count!

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.

[Zanqueta]

Hello @eliasayyy,

This is a very interesting and complex scenario because it involves capacity planning logic that is not straightforward in DAX. Let me break it down and give you a clear direction.

Key Observations:

1. Your current measure only counts patients already marked as breached (Breaching? = 1). That part is fine.
2. The next steps require:
   • Matching patients to available gaps in the scheduling table.
   • Deducting gaps as patients are allocated.
   • Forecasting which patients cannot be scheduled within the next 3 days.

This is essentially a bin-packing / scheduling optimisation problem, which is procedural in nature. DAX is not designed for iterative allocation because it works in a filter context, not in loops.

Why DAX Alone Will Struggle

• You need to consume gaps progressively as patients are assigned. DAX cannot “remember” previous allocations.
• It would require ranking patients, sorting gaps, and applying cumulative subtraction, which is extremely complex and inefficient in measures.

Recommended Approach

Move the allocation logic to Power Query or the data source, where you can:

• Filter scheduling table for Date >= Today()+1 && Date <= Today()+3.
• Group by RoomCode and calculate available gaps.
• Sort patients by required duration and try to allocate them to gaps using a custom function or M script.
• Flag patients that cannot be allocated as “Forecast Breach”.

Then, load this result into your model and simply use:

Patients Forecast Breach =
CALCULATE(
    DISTINCTCOUNT('ForecastTable'[PatientID]),
    'ForecastTable'[ForecastBreach] = TRUE()
)

Official Reference:

Power Query M formula language reference - PowerQuery M | Microsoft Learn

If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.

[Ashish_Mathur]

Hi,

Shouldn't the first answer be 4 (not 3)?  Also, shouldn't the second patient should be 1-ac (not 5-ab)?