topic Re: Add a filter to a variable in a measure in DAX Commands and Tips

Add a filter to a variable in a measure

SteveMForm — Wed, 12 Jun 2024 13:01:56 GMT

Hi Folks

I'm trying to divide the output of a variable by a figure inside a measure.

The measure is as follows:

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 was advised to filter either variable a or variable b by NETWORKDAYS. Not sure how to write this into the measure.

Overall the aim is to have a figure for each month of what capacity has been allocated. At the moment, using the variable above, it is producing a figure per month of what will be worked

The Y axis should only really go up to 40 at the max (1.0 as capacity= 1 member of staff), I have extended to show the stats for each month as they currently are

Thanks

Re: Add a filter to a variable in a measure

lbendlin — Thu, 13 Jun 2024 00:22:34 GMT

Do you have enough information to feed the NETWORKDAYS function? It needs to know what your definition of a weekend is, and you need to provide a reference to the holiday table.

Alternatively your calendar table should have a flag "working day" that you can use instead.

Re: Add a filter to a variable in a measure

SteveMForm — Thu, 13 Jun 2024 21:17:36 GMT

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

Re: Add a filter to a variable in a measure

SteveMForm — Mon, 17 Jun 2024 08:54:34 GMT

Hi Folks

any suggestions on solving this most welcome!

Thanks

Re: Add a filter to a variable in a measure

SteveMForm — Tue, 18 Jun 2024 08:16:26 GMT

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.

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)

Re: Add a filter to a variable in a measure

SteveMForm — Tue, 18 Jun 2024 08:26:10 GMT

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