Count Custom status from Profields

Hi There,
I am fairly new to eazyBI and having little problem to understand the basic syntax of COUNT function.

I have a set of custom fields at project level and I am trying to count the projects where the value of custom field Status = Completed.

Capture1

I am trying to do it this way but i am not getting the expected result.

NonZero(
Count(
Filter(
Descendants([Project].CurrentHierarchyMember, [Measures].[Profields Status]),
[Measures].[Profields Status] = “COMPLETED”
)
)
)

If anyone can shed some light will appreciate that!
Thanks,
Erik

Hi @Erik_Beltran,

Your idea to create a calculated measure to count projects by status is right.
To make this work, you might want to do two adjustments to the calculation:

  1. Add a predefined measure like “Issues created” so eazyBI would consider project relation to other dimensions in the report. The measure is the treasure!
  2. Adjust the function descendants and specify the hierarchy level for the Project dimension members. There are more details on the function: Descendants - eazyBI

The updated formula might look like this:

NonZero(Count(
  Filter(
    --set of projects
    Descendants([Project].CurrentHierarchyMember,[Project].[Project]),
    --measure to get context of other dimensions including Profield Status
    --project has at least one issue
    ([Measures].[Issues created], 
    [Time].CurrentHierarchy.DefaultMember) > 0
  )
))

This calculated measure works with the Profield dimension “Status” and counts how many projects are with each status (see picture below)

If you would like to count only COMPLETED projects, then you may add Profield status to filter criteria like this:

NonZero(Count(
  Filter(
    --set of COMPLETED projects
    Descendants([Project].CurrentHierarchyMember,[Project].[Project]),
   [Measures].[Profields Status] = "COMPLETED" AND
    --measure to get context of other dimensions
    ([Measures].[Issues created], 
    [Time].CurrentHierarchy.DefaultMember) > 0
  )
))

Best,
Zane / support@eazyBI.com

Thank you for your input Zane!!

This indeed helped me to use the correct syntax and the dimensions in my dataset. I have updated my report and created new ones following your recommendations.

Have a great day!
Thanks,
Erik