Projects count doesn't work with multiple filter

Hi, glad to join the community. We are trying to implement a global report (gauge chart, only values format) with some measures affecting multiple projects . For instance:

  • Projects count
  • Initiatives count
  • Epics count

Project dimension is being used as a page filter. Initiatives and Epics measures work fine. But Projects measures don´t work properly. We´ve tried the following alternatives for Projects count measure:

(a) NonZero(Count([Project].[Project].Members))

(b) NonZero(Count(Descendants([Project].[Project].CurrentMember, [Project].[Project])))

  • In both options, if we select All Projects it’s ok.
  • If we select only one project, option (b) is also ok but option (a) result is always All Projects result.
  • If we select more than one project, option (b) result is empty and option (a) result is always All Projects result.

Any tip for fixing the code? Thanks in advance.

Hi @JJS
The measures work as designed because the first (a) counts all members of the project dimension. The second measure (b) takes into account the CurrentMember selection thus is counting the project selection, but it cannot when multiple projects are selected.

You can try this measure to count project that have been selected in Pages:

NonZero(
  Count(
    Filter(
      ChildrenSet([Project].CurrentMember),
      ([Measures].[Issues created], 
      [Project].DefaultMember)>0
    )
  )
)

You can remove [Project].DefaultMember if you want to count how projects have issues created in the give report context:

NonZero(
  Count(
    Filter(
      ChildrenSet([Project].CurrentMember),
      [Measures].[Issues created]>0
    )
  )
)

See how the measures look in the report:

Kind regards,
Gerda // support@eazyBI.com

Thanks, Gerda.

With this improvement, the multiple selection scenario works fine now, but if you select only 1 project it counts the number of components under the selected project. Is there any alternative to address this behaviour?

Sorry for the inconvenience.

Hi @JJS ,
In that case, please try this formula:

Count(
    Filter(
      Generate( 
        {[Project].CurrentHierarchyMember,
        ChildrenSet([Project].CurrentHierarchyMember)},
        Descendants([Project].CurrentHierarchyMember,[Project].CurrentHierarchy.Levels("Project"))
        ),  
        [Measures].[Issues created]>0
  )
)

Kind regards,
Gerda