Filter through different dimension with conditions

Hi.
I’m pretty new to eazyBI and want to be able to filter out issues based on two different dimensions.
We classify our bugs with two custom fields in Jira (Probability & Severity). Then we have a matrix that provides us with how critical the bug is. For example if a bug has 4 in severity and a probability above 3 it is 75/100 critical.

I need to be able to create a filter to see the trends of different criticality of bugs. Therefore I want to create a calculated member based on these two dimensions to filter out specific bugs.

My take was a function like this:

Aggregate({
Filter([Issue].[Issue].Members,
[Severity].[Severity] = 4
and
([Probability].[probability] >= 3
)
)
})

It doesn’t work. How should I do this?

The simplest way to get a list of issues with particular custom field values would be using both dimensions Severity and Probability on Pages. There you will have an option to select Severity with value 4 and take multiple selections in Probability and select values 1,2, and 3.

If you would like to count created issues with particular values, you can use this calculated member in Mesures using both dimensions and filtering out the ones with particular values. If you address the dimension the value (name) will be represented as a string:

SUM(Filter(
  Descendants([Severity].CurrentMember,[Severity].[Severity]),
  [Severity].CurrentMember.Name = "4"),
  SUM(Filter(
    Descendants([Probability].CurrentMember,[Probability].[Probability]),
    [Probability].CurrentMember.Name MATCHES "1|2|3"),
    -- this measure in the last SUM works as a fitler and as counter 
    [Measures].[Issues created])
)

If you would like to create a calculated member in Issue dimension, then I would suggest addressing values in Severity and Probability as issue properties. Here is an example formula for this:

Aggregate(
Filter([Issue].[Issue].Members,
[Issue].CurrentHierarchyMember.GetNumber("Severity") = 4
and
[Issue].CurrentHierarchyMember.GetNumber("Probability") >= 3
))

Calculated members in Issue dimension could be easier to use in some cases. However, they could lead to some performances issues and other limitations. We suggest avoiding them, if possible and use measures or pages selections instead.

Daina / support@eazybi.com

1 Like

@daina.tupule , the solution works great when using a SUM.
However when the need is to use AVG the resulting numbers are not correct.

AVG(Filter(
Descendants([Severity].CurrentMember,[Severity].[Severity]),
[Severity].CurrentMember.Name = “4”),
AVG(Filter(
Descendants([Probability].CurrentMember,[Probability].[Probability]),
[Probability].CurrentMember.Name MATCHES “1|2|3”),
– this measure in the last SUM works as a filter and as counter
[Measures].[Issues created])
)
How can I correctly implement it with AVG?

The current formula calculates average of created issues by probability 1, 2, and 3. It seems this is not the expected behaviour.

Average could be expressed as Sum divided by Count. Most common scenarios are when you have a sum of some numeric value, for example, Story Points, Resolution days and counters - Issues. Then you can use those two in division.

However, if you would like to count average of issues. Then most probably you would like to sum issues and then divide it by some counter. For example, You can apply SUM in the formula and then apply some aggregator on top of it to calculate the average. For example, average issues with specific severity and probability by project. I will calculate the sum of particular severity and probability and apply the average by the project:

Avg(
  DescendantsSet([Project].CurrentMember,[Project].[Project]),
  SUM(Filter(
  Descendants([Severity].CurrentMember,[Severity].[Severity]),
  [Severity].CurrentMember.Name = "4"),
  SUM(Filter(
  Descendants([Probability].CurrentMember,[Probability].[Probability]),
  [Probability].CurrentMember.Name MATCHES "1|2|3"),
-- this measure in the last SUM works as a filter and as counter
[Measures].[Issues created])
)
)

Daina / support@eazybi.com