How to create two inverse calculated members

[Leeland]

eazyBI logic has me stuck.

I can create a calculated member of all the tickets I want in total with this:

Aggregate(
  {
    [Status].[In Progress],
    [Status].[Ready for Review],
    [Status].[Awaiting IT Completion of Security Controls & Activities],
    [Status].[Awaiting Questionnaire from IT],
    [Status].[Awaiting Information/Documentation from IT]
  }
)

But, what I want to do is take that above set and break it into two calculated members based on if the issues have the label "Dormant".

Issues Not Dormant:

Aggregate(
  Filter(
    {
      [Status].[In Progress],
      [Status].[Ready for Review],
      [Status].[Awaiting IT Completion of Security Controls & Activities],
      [Status].[Awaiting Questionnaire from IT],
      [Status].[Awaiting Information/Documentation from IT]
    },
    [Label] != "Dormant"
  )
)

Dormant Issues:

Aggregate(
  Filter(
    {
      [Status].[In Progress],
      [Status].[Ready for Review],
      [Status].[Awaiting IT Completion of Security Controls & Activities],
      [Status].[Awaiting Questionnaire from IT],
      [Status].[Awaiting Information/Documentation from IT]
    },
    [Label] = "Dormant"
  )
)

Every variation I try from Google and the docs either ends up with ALL the issues or none of the issues.

Thanks in advance!

[Ilze Leite-Apine · Accepted solution]

Hi @Leeland 

You may want to create calculated measures (in "Measures"), that uses your calculated member in "Status" dimension together with the label and measure "Issues created".

Calculated member in the "Status" dimension would the same as you mentioned - the aggregate of all needed statuses. For clarity, let's call it "Progress statuses" in the following calculations)

1. To get all issues that are in the given statuses and with the label "Dormant" create a measure with a tuple: https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-measures-and-members/calculated-measures#Calculatedmeasures-Tuples

([Measures].[Issues created],
[Status].[Progress statuses],
[Label].[Dormant])

2. To get the rest of the issues, create another measure where you use mathematical subtraction between two tuples: all issues in progress statuses and those with the label:

([Measures].[Issues created],
[Status].[Progress statuses],
[Label].DefaultMember)

-

([Measures].[Issues created],
[Status].[Progress statuses],
[Label].[Dormant])

Use your calculated member name instead of "Progress statuses"!

However, dealing with labels and their combinations is quite a tricky thing as they are multiple value fields. If this solution is not what you were looking for,  please contact support@eazybi.com, there are other approaches available how to get the data you need.

Ilze // support@eazybi.com

[Fabian Lim]

@Leeland

I recommend you post this in the eazybi community. Their staff is really fast in gettig back with a solution.

Cheers.