How to count workdays per assignee

Questions & Answers
1

Hey,
i’m new in EazyBI so i would be grateful for any help.

I need to find a number of workdays per person (Assignee). I thought about counting days where there was at least one closed issue by this person.
I used IIF to make 1 when conditions above are true and 0 when not.
IIf(([Measures].[Issues closed],[Assignee].CurrentMember)>0,1,0)
It works, but only with Day hierarchy, but i need to sum it to months. When i change hierarchy to Months it gives me 1 or 0, not a sum. I think Aggregate could help but i have problem to write it properly.

Thanks for any help!

2

Hi,

The if condition returns 1 or 0 based on if there are Issues closed in selected level/context - in Month the Issues closed is greater than zero so it returns 1.

To do the filter on lowest level of Time (days) and then count the set of matching days on the higher level you would use the Descendants function (see set functions here https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-members/mdx-function-reference#MDXFunctionReference-SetFunctions). The following formula should work as expected

Count(Filter(
  Descendants([Time].CurrentHierarchyMember,
    [Time].CurrentHierarchy.Levels("Day")
), [Measures].[Issues closed] > 0 ))

Note that you can wrap it in NonZero(…) to avoid showing zeroes for days that do not match the condition.
Lauma / support@eazybi.com

1 Like
3

It works! Thank you very much!

1 Like