Help with Capacity Modeling

[JBF1978]

I'm working on building a Power BI model to visualize our technicians’ weekly schedules. The goal is to show when each technician is assigned to work, PTO, training, meetings, etc., so we can easily see who is available and who is not—and why.

I’m using data from a table called FSA CalendarAppointments, which includes a Type_CalendarAppointmentTypeID field that categorizes each appointment:

• -1 = Work

• -2 = Training

• -3 = Meetings

• -5 = PTO

This table is linked to another table called FSA Resources, which contains our list of currently schedulable technicians.

What I want to create is a matrix view by technician and by week, starting on Sunday, where I can:

• See each technician’s schedule for the week

• Identify who is booked and for what reason

• Spot gaps where a technician is available

• Eventually dive deeper to understand capacity constraints

Additionally, I’d like to create some sort of slider or slicer that allows me to adjust the capacity model up or down. For example, if we’re running at 80%, I want the model to reflect that we have some flexibility. But if I turn it up to 100%, it should highlight the weeks where we are at or over capacity.

I’ve tried building a matrix to display this, but the results aren’t matching expectations—some values are missing or not accurately reflecting what’s in the data.

Has anyone built something like this before or have tips on how to structure the model, DAX, or visuals to make this work?

This is the DAX I used to create the table:  

TechnicianWeeklyCapacity =

ADDCOLUMNS(

    CROSSJOIN(

        VALUES('FSA Resources'[ResourceID]),

        VALUES('CalendarWeeks'[WeekStartDate])

    ),

    "HasWork",

        CALCULATE(

            IF(

                COUNTROWS(

                    FILTER(

                        'FSA CalendarAppointments',

                        'FSA CalendarAppointments'[Type_CalendarAppointmentTypeID] = -1 &&

                        'FSA CalendarAppointments'[Resource_ResourceID] = SELECTEDVALUE('FSA Resources'[ResourceID]) &&

                        'FSA CalendarAppointments'[StartOn] >= SELECTEDVALUE('CalendarWeeks'[WeekStartDate]) &&

                        'FSA CalendarAppointments'[StartOn] < SELECTEDVALUE('CalendarWeeks'[WeekStartDate]) + 7

                    )

                ) > 0, 1, BLANK()

            )

        ),

    "HasPTO",

        CALCULATE(

            IF(

                COUNTROWS(

                    FILTER(

                        'FSA CalendarAppointments',

                        'FSA CalendarAppointments'[Type_CalendarAppointmentTypeID] = -5 &&

                        'FSA CalendarAppointments'[Resource_ResourceID] = SELECTEDVALUE('FSA Resources'[ResourceID]) &&

                        'FSA CalendarAppointments'[StartOn] >= SELECTEDVALUE('CalendarWeeks'[WeekStartDate]) &&

                        'FSA CalendarAppointments'[StartOn] < SELECTEDVALUE('CalendarWeeks'[WeekStartDate]) + 7

                    )

                ) > 0, 1, BLANK()

            )

        ),

    "HasTraining",

        CALCULATE(

            IF(

                COUNTROWS(

                    FILTER(

                        'FSA CalendarAppointments',

                        'FSA CalendarAppointments'[Type_CalendarAppointmentTypeID] = -2 &&

                        'FSA CalendarAppointments'[Resource_ResourceID] = SELECTEDVALUE('FSA Resources'[ResourceID]) &&

                        'FSA CalendarAppointments'[StartOn] >= SELECTEDVALUE('CalendarWeeks'[WeekStartDate]) &&

                        'FSA CalendarAppointments'[StartOn] < SELECTEDVALUE('CalendarWeeks'[WeekStartDate]) + 7

                    )

                ) > 0, 1, BLANK()

            )

        ),

    "HasMeeting",

        CALCULATE(

            IF(

                COUNTROWS(

                    FILTER(

                        'FSA CalendarAppointments',

                        'FSA CalendarAppointments'[Type_CalendarAppointmentTypeID] = -3 &&

                        'FSA CalendarAppointments'[Resource_ResourceID] = SELECTEDVALUE('FSA Resources'[ResourceID]) &&

                        'FSA CalendarAppointments'[StartOn] >= SELECTEDVALUE('CalendarWeeks'[WeekStartDate]) &&

                        'FSA CalendarAppointments'[StartOn] < SELECTEDVALUE('CalendarWeeks'[WeekStartDate]) + 7

                    )

                ) > 0, 1, BLANK()

            )

        ),

    "IsOpen",

        IF (

            CALCULATE (

                COUNTROWS (

                    FILTER (

                        'FSA CalendarAppointments',

                        'FSA CalendarAppointments'[Type_CalendarAppointmentTypeID] IN { -1, -2, -3, -5 } &&

                        'FSA CalendarAppointments'[Resource_ResourceID] = SELECTEDVALUE('FSA Resources'[ResourceID]) &&

                        'FSA CalendarAppointments'[StartOn] >= SELECTEDVALUE('CalendarWeeks'[WeekStartDate]) &&

                        'FSA CalendarAppointments'[StartOn] < SELECTEDVALUE('CalendarWeeks'[WeekStartDate]) + 7

                    )

                )

            ) = 0,

            1,

            BLANK()

        )

)

This is the result of that, which it's showing Everyone is open for each week:

Any help would be greatly appreciated. 

[burakkaragoz]

Hi @JBF1978 ,

Capacity modeling can get tricky, but a good starting point is to look at:

• Number of users (concurrent vs total)
• Report complexity (heavy visuals, big datasets, etc.)
• Refresh frequency (how often your data updates)

Microsoft has a Power BI Capacity Planning whitepaper that breaks this down really well. You can also use the Power BI Premium Capacity Metrics app to monitor usage and get a feel for what you might need.

If you're embedding or using Fabric capacities, the same logic applies — just make sure you're sizing based on actual usage patterns.

Let me know if you want help estimating based on your specific scenario!

---

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.

[v-bmanikante]

Hi  @JBF1978 ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Please don't forget to give a "Kudos " – I’d truly appreciate it!

Regards,

B Manikanteswara Reddy
 

[v-bmanikante]

Hi @JBF1978 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Please don't forget to give a "Kudos " – I’d truly appreciate it!

Regards,

B Manikanteswara Reddy

[v-bmanikante · Accepted solution]

Hi @JBF1978 ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Please don't forget to give a "Kudos " – I’d truly appreciate it! Also, please let your team know that we proudly support all other communities. If anyone needs help, we’re more than happy to assist, just reach out!

Regards,

B Manikanteswara Reddy

[v-bmanikante]

Hello @JBF1978 ,

Thank you for reaching out to Microsoft Fabric Community Forum.

Could you please try the below DAX query to create a calculated Table:

TechnicianWeeklyCapacity =
ADDCOLUMNS (
CROSSJOIN (
VALUES ( 'FSA Resources'[ResourceID] ),
VALUES ( 'CalendarWeeks'[WeekStartDate] )
),

"HasWork",
VAR ResourceID = [ResourceID]
VAR WeekStart = [WeekStartDate]
RETURN
CALCULATE (
IF (
COUNTROWS (
FILTER (
'FSA CalendarAppointments',
'FSA CalendarAppointments'[Type_CalendarAppointmentTypeID] = -1 &&
'FSA CalendarAppointments'[Resource_ResourceID] = ResourceID &&
'FSA CalendarAppointments'[StartOn] >= WeekStart &&
'FSA CalendarAppointments'[StartOn] < WeekStart + 7
)
) > 0,
1,
BLANK()
)
),

"HasPTO",
VAR ResourceID = [ResourceID]
VAR WeekStart = [WeekStartDate]
RETURN
CALCULATE (
IF (
COUNTROWS (
FILTER (
'FSA CalendarAppointments',
'FSA CalendarAppointments'[Type_CalendarAppointmentTypeID] = -5 &&
'FSA CalendarAppointments'[Resource_ResourceID] = ResourceID &&
'FSA CalendarAppointments'[StartOn] >= WeekStart &&
'FSA CalendarAppointments'[StartOn] < WeekStart + 7
)
) > 0,
1,
BLANK()
)
),

"HasTraining",
VAR ResourceID = [ResourceID]
VAR WeekStart = [WeekStartDate]
RETURN
CALCULATE (
IF (
COUNTROWS (
FILTER (
'FSA CalendarAppointments',
'FSA CalendarAppointments'[Type_CalendarAppointmentTypeID] = -2 &&
'FSA CalendarAppointments'[Resource_ResourceID] = ResourceID &&
'FSA CalendarAppointments'[StartOn] >= WeekStart &&
'FSA CalendarAppointments'[StartOn] < WeekStart + 7
)
) > 0,
1,
BLANK()
)
),

"HasMeeting",
VAR ResourceID = [ResourceID]
VAR WeekStart = [WeekStartDate]
RETURN
CALCULATE (
IF (
COUNTROWS (
FILTER (
'FSA CalendarAppointments',
'FSA CalendarAppointments'[Type_CalendarAppointmentTypeID] = -3 &&
'FSA CalendarAppointments'[Resource_ResourceID] = ResourceID &&
'FSA CalendarAppointments'[StartOn] >= WeekStart &&
'FSA CalendarAppointments'[StartOn] < WeekStart + 7
)
) > 0,
1,
BLANK()
)
),

"IsOpen",
VAR ResourceID = [ResourceID]
VAR WeekStart = [WeekStartDate]
RETURN
VAR HasAny =
CALCULATE (
COUNTROWS (
FILTER (
'FSA CalendarAppointments',
'FSA CalendarAppointments'[Type_CalendarAppointmentTypeID] IN { -1, -2, -3, -5 } &&
'FSA CalendarAppointments'[Resource_ResourceID] = ResourceID &&
'FSA CalendarAppointments'[StartOn] >= WeekStart &&
'FSA CalendarAppointments'[StartOn] < WeekStart + 7
)
)
)
RETURN IF ( HasAny = 0, 1, BLANK() )
)

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,

B Manikanteswara Reddy