Custom measure equivalent of a page filter

Questions & Answers
1

Dear Community,

I would like to ask for your help in understanding the following topic. For several reasons, I would need to create a custom measure that would act as a page filter (I need one dimension of the data filtered, the other unfiltered in the same analysis). I would like to sum up the Story Points due measure, for the issues that belong to a certain Fix Version. For that I created the following snippet of code:

Aggregate(
Filter(
      [Measures].[Story Points due],
      [Fix Version].CurrentMember.Name MATCHES '.*Some expression.*'
      )
)

However, when run, it returns empty. Even more interestingly, when I add the Fix Version dimension to the rows, it returns with the correct values for the Fix Version I entered in the search pattern, but when the Fix Version dimension is removed, it is empty again. So as I can see, the

 [Fix Version].CurrentMember.Name

expression behaves differently when Fix Version is an active dimension on the Rows, and when it is not.

Do you have a hint how I could filter the data without using the Fix Version dimension on the Rows?

Thanks for your support,
Márton

2

Hi, @hidvegm,

I would recommend keeping Measures on the report columns and using dimensions for filtering data.
There are two solutions you might consider.

Option 1. The specified Fix Version is applicable to all metrics in the report, “Story Points due” and others you have selected for your report.
In this case, define a new calculated member in the “Fix Version” dimension and aggregate fix versions matching a particular expression (more details on calculated members in the documentation: Calculated members in other dimensions).
The expression might look like this:

Aggregate(
  Filter(
    [Fix Version].[Version].Members,
    [Fix Version].CurrentMember.Name MATCHES ".*Some expression.*"
  )
)

Then, add “Fix Version” dimension to report pages and select the newly created member as a filter.

Option 2. Only measure “Story Points due” should represent value for specific fix versions.
In this case, you might want to define a new calculated measure in Measures. For the calculation, Filter fix version matching the name pattern and sum up measure “Story Points due” (more details on the calculated measure in the documentation: Calculated measures).
The expression might look like this:

Sum(
  --set of Version matching name pattern
  Filter(
    [Fix Version].[Version].Members,
    [Fix Version].CurrentMember.Name MATCHES ".*Some expression.*"
  ),
  --sum up story points due
  [Measures].[Story Points due]
)

Best,
Zane / support@eazyBI.com

3

Hi @zane.baranovska,

thanks for the tip. I used option two and now it is working properly. I see now what I have missed.

Bests,
Márton

1 Like