Hi all,
I am trying to understand how to obtain a set (or a count) of issues that have the same value as that of a given row in rows with issues.
For example, I have all issues in rows, and wanted to display a Measure that describes the count of all issues that have the same value for a custom field (which is imported as an issue property).
Thanks for the help!
Hello @dEEE !
See this: Getting the count of specific value in given field
There are several methods to count (aggregate, count, filter, tuples…), they’re nicely described in the topic above.
Hope it helps!
Regards,
Vasile S.
hi @VasileS, thanks for the quick response.
The issue I am having is on the comparison for the given row’s Requirement Number in the example shown vs the Requirement Number of all other issues. Evaluating [Measures].[Issue Requirement Number] vs [Issue].CurrentHierarchyMember.GetString(‘Requirement Number’) is not correct as it appears to simply be evaluating against itself and I need the criteria to be dynamic per the row number, and not static (e.g. commented comparison).
Any thoughts? The image shows an example of the desired result.
Count(
Filter(
Descendants([Issue].[Issue].Members, [Issue].[Issue]),
[Issue].CurrentHierarchyMember.GetString(‘Requirement Number’) = [Measures].[Issue Requirement Number]
–“S.2.x.2” = [Issue].CurrentMember.GetString(‘Requirement Number’)
–“S.2.x.2” = [Issue].CurrentHierarchyMember.GetString(‘Requirement Number’)
)
)
Hi @dEEE,
Would you consider importing the custom field as a dimension?
That would allow using the imported dimension to split the issues by the value of that custom field.
Then you might use the Tuple to slice the dataset by the specific value of the field or use the dimension on report rows or columns together with a relevant measure.
Please see below the example of a tuple that finds the number of issues with the current Requirement Number “S.2.x.2” within the current report context if Requirement Number is imported as a dimension.
([Requirement Number].[S.2.x.2],
[Measures].[Issues created])
If that does not fit you, please explain what do you mean by
“I need the criteria to be dynamic per the row number”
Regards,
Oskars / support@eazyBI.com
Hi @oskars.laganovskis ,
I have to reach out to the admins to confirm if the custom field can be imported as a dimension, as I am only currently able to select import as a property.
What I meant by “I need the criteria to be dynamic per the row number” was that I want to show the count of matching Requirement Number for that given issue on the report results rather than specifying a static string to match. Is this possible?
Hi @dEEE,
If you want to be able to change the “Requirement number” in scope, you might use the imported dimension on the Page filters or the report Rows.
Regards,
Oskars / support@eazyBI.com
Hi @oskars.laganovskis ,
As I work with the admin to see if a “Label Manager” field type can be imported as a dimension, is importing “Requirement Number” as a dimension the only solution or is there an alternative with only importing as a property?
Hi @dEEE,
Importing custom field as a dimension is the most straightforward and efficient option to allow the report or dashboard user change the selection in the Page filters.
Workarounds might include creating calculated measures that iterate through the Issue dimension and then moving the Measures dimension to the Page filter. Although, they might work at the beginning, soon you would call for a faster or more efficient option unless you work on a very small dataset.
Regards,
Oskars / support@eazyBI.com
hi @oskars.laganovskis ,
Would you mind sharing how that would be done? I had the same thought process in looking to loop through all issues for each Row in example above, but not sure how that is actually done.
Hi @dEEE,
The main problem in your case is that the MDX only allows one CurrentHierarchyMember, and you cannot reach a member from the same hierarchy and level from a higher iteration level.
The workaround is to address the same issue through another hierarchy.
This workaround might be limited to one “secondary” hierarchy level.
Please see below an example of switching to the Epic hierarchy to save the initial issue and iteration through the standard hierarchy within the Issue dimension. This expression would only retrieve the number of issues with the same “Requirement number” for parent level issues.
NonZero(
Sum(
--saving the current row issue to another hierarchy
[Issue.Epic].[Parent].GeTMemberbyKey(
[Issue].CurrentHierarchyMember.Key),
--actually counting issues
Count(
Filter(
--all issues
[Issue].[Issue].Members,
--comparing value of current issue in iteration
[Issue].CurrentMember.GetString('Requirement Number')
=
--against the value of the initial issue - retrieving from different hierarchy
[Issue.Epic].CurrentMember.GetString('Requirement Number')
)
)
))
Reards,
Oskars / support@eazyBI.com