Having troubles solving the below situation:
Issues have a numeric custom field “Impacted Transactions”.
Additionally one Issue might have one or several “Regulations” (another custom field)attached to it. We are using “Regulations” in filtering, so when several “Regulations” are selected - amount of transactions is incorrect. Example:
Issue-X
Regulations: Alfa, Delta
Impacted Transactions: 4
I select multiple “Regulations” in the filter. As a result I get “Impacted Transactions”: 8 for this one issue, because in the filter have selected Alfa and Delta, so transactions became 4+4. How should I only show transactions per ticket no matter the filter options?
Thinking that “Issue Impacted transaction” should be used…
Solved similar problem where I needed to count distinct issues - used Distinct Issues count. However, unsure what custom formula to use in this scenario.
A multi-select field may multiply the measure value when used as a multi-selection page filter or calculated member. For example, if several multi-select field values are selected in a report, and an issue belongs to two, then the measure value is duplicated for this issue.
eazyBI offers a set of Distinc issue count measures to cover most of the use cases when looking for issue count. However, for a numeric custom field, such distinct measures are not created.
In your case, you might want to define a new calculated measure that would aggregate impacted transactions without publications for issues matching the filter criteria. The calculations would go through all issues and check if the issue has any value for the custom field (in your case, “Impacted transactions”) and matches the report context (filter criteria and values on report rows and columns).
Sum(
--go through the set of issues and filter ones with a numeric value
Filter(
Descendants([Issue].CurrentMember,[Issue].[Issue]),
NOT IsEmpty([Issue].CurrentMember.Get('Impacted transactions') )
),
CASE WHEN --issue matches report context
[Measures].[Impacted transactions created] > 0
THEN --get custom field value as it is assigned to issue without duplications
DefaultContext((
[Measures].[Impacted transactions created],
[Issue].CurrentMember
))
END
)
Before using the code, please validate and update the property and measure names to match your eazyBI. Use autocomplete in the formula editor to get the right property and measure names.
More details on calculated measures and used functions are here:
