Dedupe Custom Field Changes

[Matt_Shudy]

I’m having troubles getting distinct values for custom field changes when multiple page filters/dimensions are selected. I would like to show the total change to a custom field over the last 30 days. I’ve reviewed and implemented the dedupe function found in the link below for many standard value fields and it works great, is there a similar function that would work with value changes? As written below, the values are doubled when multiple selections are made.

SUM(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
NOT isempty([Measures].[Est To Close ($) change])
),
CASE WHEN [Measures].[Issues created] > 0
THEN
[Measures].[Est To Close ($) change]
END
)

Dedupe based on customer field

I have a report that shows remaining story points per person. the row(s) are assignee and the column is story points. There is a dimension in place as I need to select multiple customers and exclude others. the issue is that 1 issue is being counted multiple times as we have a custom field called “customer”. This is a multi select … if you select 1 customer you get the right point value … if you select multiples it multiplies. I need to only bring in the story points once. i.e. where I exp…

[zane.baranovska]

Hi @Matt_Shudy,

Yes, when filtering data by multi-selection field and selecting multiple values in a report, then results are summed up for each matching value.

In the given example, Dedupe based on customer field is used an issue property “Issue Story Point” and a measure “Issues created” representing current values. In your case, you are analyzing historical data and their changes (Est To Close ($) change) and might want to use historical measures for the calculation.

The logic for calculation to exclude the duplicates is to get a set of issues that matches report context including selected values for a multi-selection field, and then sum up those values ignoring the selection of the multi-selection field. The updated formula right look like this:

Sum(
  --get set of issues that has changes in Est To Close ($) according to report context
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    [Measures].[Est To Close ($) change] > 0
  ), --sum up changes in Est To Close ($) ignoring multi selection custom field
  ([Measures].[Est To Close ($) change],
  [Multi-selection custom field].CurrentHierarchy.DefaultMember)
)

There are more details on calculated measures and used function DefaultMember:

• https://docs.eazybi.com/eazybijira/analyze-and-visualize/calculated-measures-and-members/calculated-measures
• https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-measures-and-members/mdx-function-reference/defaultmember

Best,
Zane /support@eazybi.com

[Matt_Shudy]

Hi Zane, thank you very much for the explanation and solution! Works great!

I have a follow up question, are dates stored within eazyBI for the changes? If so, how would a filter be applied to only show the changes that happened within the last X days?

Thank you, Matt

[Matt_Shudy]

Hi Zane, I believe I had a erroneous filter that was causing not expected results to my report.

Thanks again! Matt

[zane.baranovska]

Yes, the changes are represented on the timeline. For example, you might use Time dimension on pages or rows to see the value change in each period.