Forum Discussion
Issue on filter on Resource capacity analysis
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'
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?
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 XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- suparnababu8
Super User
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! 😊
If I remove the ALL function, all my data desapears :(. Table and graph returns on blank).
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 XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
