How to filter on non aggregated values

Questions & Answers
1

Hi, I have a set of data (not from JIRA) - each row from the database contains various dimensions and some measured values. I only want the rows in which the measured value for a given dimension meet some criteria. When I try to use Aggregate and Filter function, I get more data back than I expect because the dimensions I am using in the report contain many individual ‘rows’ from the original dataset. Hence, the sum of the measured values exceeds my criteria - so it is as if I want a new cube that only contains specific rows that I can then aggregate on. Here is an example:

Row 1 D1=A, D2=X, D3=unique index value, M=5
Row 2 D1=A, D2=Y, D3=unique index value, M =6
Row 3 D1=B, D2=Y, D3=unique index value, M =11

Aggregate(Filter(
[D3].AllMembers,
[Measures].[M]>10)
))

Should return only Row 3 when I am looking at D1 but I get another row back because 5+6=11 (greater than 10), In SQL I would include M in the WHERE clause and GROUP BY D1, D2

2

eazyBI sums up any value for measures, and you would like to apply the formula for a particular report context.

You are referencing to AllMembers in your formula. It will give you any member in the dimension, including All D3s, any calculated member, etc. They will be counted in with this measure as the sum value will be more than one member value.

I would suggest this formula for a calculated member in Measures :

Count( 
   Filter( 
      Descendants([D3].CurrentMember,[D3].[D3]), 
      [Measures].[M] > 10 
) )

This formula will work for default members in D3 dimension only and will give you a count of D3 members with a value > 10 for any combinations of members (D1 and D2) you will use in the report.

Daina / support@eazybi.com