Calculating remaining Capacity, if Employee has no work then return the

Hi folks,

Please can you take a look at the below and see if you can help me resolve this issue, If you need anymore details please let me know.

Problem Statement: I'm encountering an issue with a DAX measure in my Power BI model where it's not returning the expected result of 5 (Max Capacity) when an employee has no work assigned to them that week.

Background:

• I have a Power BI model that involves two tables: F_Capacity and DIM_Team_Details.
• The measure in question, called "Remaining Capacity," calculates the remaining capacity for each employee and week based on certain conditions, including "Consumed Days."
• The intention is to return 5 when "Consumed Days" is blank or zero, indicating that there's no work for that week.

What I've Tried:

• I've attempted various DAX formulas and measures to explicitly handle blank or zero values in "Consumed Days" but haven't achieved the desired result.
• I've ensured that the "Consumed Days" column is of the correct data type (numeric) in both tables.
• I've validated the data and confirmed that there are indeed cases where "Consumed Days" is blank or zero.

Example of the Measure: Here is the DAX measure that I've been using:

Remaining Capacity = 
VAR MaxCapacityTable = SUMMARIZE(DIM_Team_Details, DIM_Team_Details[EmployeeID], DIM_Team_Details[Max Capacity], DIM_Team_Details[Start Date], DIM_Team_Details[End Date])
RETURN
SUMX(
ADDCOLUMNS(
SUMMARIZE(
F_Capacity,
F_Capacity[EmployeeID],
DIM_Calendar[Week_Commencing]
),
"Max Capacity",
MAXX(FILTER(MaxCapacityTable, F_Capacity[EmployeeID] = DIM_Team_Details[EmployeeID]), DIM_Team_Details[Max Capacity]),
"Start Date",
MINX(FILTER(MaxCapacityTable, F_Capacity[EmployeeID] = DIM_Team_Details[EmployeeID]), DIM_Team_Details[Start Date]),
"End Date",
MAXX(FILTER(MaxCapacityTable, F_Capacity[EmployeeID] = DIM_Team_Details[EmployeeID]), DIM_Team_Details[End Date])
),
IF(
[Start Date] > [Week_Commencing] || [End Date] < [Week_Commencing],
0,
IF(
ISBLANK([Consumed Days]) || [Consumed Days] = 0,
5,
IF(
[Consumed Days] <= [Max Capacity],
[Max Capacity] - [Consumed Days],
0
)
)
)
)

Below are the tables I am using

Finally this is the script that I am using for the measure Consumed Days

Consumed Days = 

SUMX(

    SUMMARIZE(

        F_Capacity,

        F_Capacity[EmployeeID],

        DIM_Calendar[Week_Commencing],

        "Consumed Days", SUM(F_Capacity[Days_Worked])

    ),

    [Consumed Days]

)

 

Relationships below

 

 

 

Thank you in advance for taking a look at this.