Forum Discussion
Add a filter to a variable in a measure
Hi lbendlin - great to see you
The dimDates table has:
Weekday - Sunday as 0, Monday as 1 etc, running through the week
WeekdayWeekend - Weekday and Weekend choice
IsBankHoliday - True or False choice
I checked on one individual staff who had just one work item for a given month of 0.5 capacity allocated to work and her current monthly figure 'worked' is 10.5. This is not correct as the current measure is multiplying this 0.5 by the working days ie 21 x 0.5 = 10.5.
The figure that staff (therefore the Y axis figure) should have for that month should be 0.5.
This should work the same if moving up a level to Deputy Director, and then without any column legend giving the whole teams capacity allocated for a given month, instead of multiplying the total capacity by the number of working days.
If you could show how we amend the measure to correctly show capacity allocated instead of capacity 'worked', that would be great lbendlin
Thanks
Hi Folks
any suggestions on solving this most welcome!
Thanks
- SteveMForm2 years ago
Helper III
Still trying to solve this:
I have the current measure which is calculating the capacity allocated (but incorrectly adding up each staff members capacity for each day in a month eg:
0.5 for each day in a 31 day month is being mutlplied as 0.5 x 31 = 15.5, whereas it should be 0.5 for the month.
Capacity allocated = var a = SUMMARIZECOLUMNS('Resourcing'[Name],'Resourcing'[Start Date],'Resourcing'[End Date],'Resourcing'[Capacity],'dimDates'[Date]) var b = ADDCOLUMNS(a,"sm",if([Start Date]<=[Date] && [Date]<=[End Date],[Capacity])) return sumx(b,[sm])I have 2 options it seems now:
1. rewrite the measure so it calculates correctly
2. use NETWORKDAYS to divide one of the variables in the above measure (or some other way) to get the correct capacity allocated.
In theory, number 2 works, but not sure a) if its possible and b) if it is the best way to calculate the capacity allocated for any time measure for however many staff have capacity allocated.
I have the NETWORKDAYS working outside of the above measure, but using dates from a different table (as the Resourcing table is pivoted) and applying on data outside of the resourcing table, not sure if it will apply to the start and end dates in the Resourcing table.
Working Days = Var Start_Date = SELECTEDVALUE('Work Tracker'[Start Date]) var End_Date = SELECTEDVALUE('Work Tracker'[End Date]) RETURN NETWORKDAYS(Start_Date, End_Date)- SteveMForm2 years ago
Helper III
Zubair_Muhammad - not sure if you could help with this one- I saw you had a solution to a related problem here: Solved: Using a Measure as a filter in DAX formula - Microsoft Fabric Community
Thanks