Issue on filter on Resource capacity analysis

[bslusars]

I need to create line graph for Resource Capacity. In my table 'resource' I have Members, Team, %of Capacity, InitialDate and Final date.

Member	Team	% of Capacity	InitialDateCapacity	FinalDateCapacity
John	Team A	50	1-1-2024	12-31-2030
Paul	Team B	70	1-1-2024	12-31-2030
Martha	Team A	75	1-1-2024	12-31-2030

I created a calendar table 'TabelaDatas' to create my graphy by month.

I could generate the graph with total capacity, applying the measure below 'TotalCapacityHours'

TotalCapacityHours =

CALCULATE(

    (160 * SUMX('resource', VALUE('resource'[% of Capacity]))) / 100,

    FILTER(

        ALL('resource'),

        DATEVALUE('resource'[InitialDateCapacity]) <= MAX('TabelaDatas'[Date]) &&

        DATEVALUE('resources'[FinalDateCapacity]) >= MIN('TabelaDatas'[Date])))

Now, I cannot add filters by Member and Team, if I add the slicer, it doesn't apply into the graph.

I've already tried to remove the ALL from the measure, and the hole data desapears. 😞

Any help to apply the filters?

[v-xuxinyi-msft · Accepted solution]

Hi @bslusars 

Thanks for the reply from suparnababu8 .

@bslusars , I have a few questions to confirm with you. According to this sentence, "I created a calendar table 'TabelaDatas' to create my graphy by month." Is your calendar table just to make the x-axis of the line graph in months? If so, can you consider presenting it in the following form?

Put InitialDateCapacity in, select the hierarchy, and keep only Month.

The reason why there is data for February here is that I added data for February to the sample data you gave.

And this part of the formula, “(160 * SUMX('resource', VALUE('resource'[% of Capacity]))) / 100”,  is it the sum of each month * 160 and then divided by 100?

In my test, I used the InitialDateCapacity column as the x-axis of the chart and Slicer was applied normally.

Please feel free to correct me if I have any misunderstanding about your requirements. It would be even better if you can provide sample data of the calendar table and your expected results. How to provide sample data in the Power BI Forum - Microsoft Fabric Community Or show them as screenshots or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[suparnababu8]

Hi @bslusars 

It sounds like you’re almost there! The issue seems to be with the use of the ALL function, which removes all filters from the ‘resource’ table, making it difficult to apply slicers for Members and Teams. Let’s adjust your measure to respect the slicers.

Try modifying your measure like this:

TotalCapacityHours =
CALCULATE(
(160 * SUMX('resource', VALUE('resource'[% of Capacity]))) / 100,
FILTER(
'resource',
DATEVALUE('resource'[InitialDateCapacity]) <= MAX('TabelaDatas'[Date]) &&
DATEVALUE('resource'[FinalDateCapacity]) >= MIN('TabelaDatas'[Date])
)
)

By removing the ALL function and directly filtering the ‘resource’ table, the slicers should now work as expected.

Give this a try and let me know if it resolves the issue! 😊

[bslusars]

If I remove the ALL function, all my data desapears :(. Table and graph returns on blank).