# Aggregate Transition Status, where all data is present

Hello,

Please, help me with the following function.
We have table with ‘Workdays in transition status’, grouped by Transition Status. Per each issue.

Last 4 columns - aggregation of several different transition statuses. Currently, they show total by all presented. So, if only 1 Status from 5 is presented, it will contain value of this 1 Status.
What we’d like to have: Show the value only in case the presence of all Statuses, which participate in concrete aggregation. So, if 4 from 5 are presented - field will be empty until last Status data is present.

The current formula for aggregation:

``````   Aggregate({
[Transition Status].[New],
[Transition Status].[Cost Estimation],
[Transition Status].[Need Clarification],
[Transition Status].[Waiting for Priority],
})
``````

How can I do it?

Instead of using the Workdays in transition status measure, you can define a new measure that shows the workdays in transition status only if they are filled in for all statuses. Here is a formula that would do this (see comments inline):

``````CASE WHEN -- when there is more than one status in aggregation
Count(ChildrenSet([Transition Status].CurrentMember)) > 1
THEN
CASE WHEN -- count if all the statuses have workdays in transition status
Count(Filter(ChildrenSet([Transition Status].CurrentMember),
NOT IsEmpty([Measures].[Workdays in transition status])))
= Count(ChildrenSet([Transition Status].CurrentMember))
THEN Val([Measures].[Workdays in transition status]) -- if yes, show the workdays
END
ELSE -- if there is only one status, show it's workdays in transition status
NonZero([Measures].[Workdays in transition status])
END
``````

Lauma / support@eazybi.com

@lauma.cirule, thanks a lot!

1 Like

Is there someplace I could get an export definition for a report like that?