Count distinct issues based on criterions

Hi,

I’m trying to count (distinct) the number of issues but based on several criterions, so I tried this:

NonZero(Count(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
[Measures].[Issue resolution] = [Resolution].[Won’t Do] OR
not IsEmpty( ([Measures].[Transitions to status],[Transition Status].[Transferred]) ) OR
not IsEmpty( ([Measures].[Transitions to status],[Transition Status].[Delivered]) )
)
))

But I got nothing, no result !

Any idea how to solve this ?

Thx

Hi @cdemez,

Iteration through all issues is sometimes the only option for finding a distinct number of issues compliant with multiple overlapping conditions.

The conditions used here, however, might need a little improvement.

The first condition of checking the resolution might fail due to inconsistent formatting.
The measure “Issue resolution” returns the string value - the name of the resolution. While the member [Resolution].[Won’t Do] returns a numeric value - the number of issues with the specific resolution as the default measure “Issues created” is always applied when the measure is not provided.

The following syntax might work better.

[Measures].[Issue resolution] = "Won't Do"

However, that does not check the relevance of the issue to the report context and might return false-positive issues.

A tuple checking the proper issue resolution might work better. The updated condition could be

([Measures].[Issues resolved],
 [Resolution].[Won't Do])>0

The next two conditions include first the retrieval of the value and then checking if the value was retrieved for two measures. The number of transitions to or from any status is either empty for no transitions or a natural number. Therefore, you might group both conditions mathematically and then check the presence of the value.

The improved version of these two conditions grouped might be as follows.

(
 ([Measures].[Transitions to status],
  [Transition Status].[Transferred])
+
([Measures].[Transitions to status],
 [Transition Status].[Delivered])
 ) >0

However, the main drawback of iteration is the time required to analyze each issue in larger data cubes.
Therefore, it is worth looking if the iteration might be replaced by categorization and aggregated figures.

The possible overlapping of sets within the same dimension occurs for issues having transitions to completion statuses “Transferred” or “Delivered”.
You might group these statuses into one calculated member “Completion statuses” in the “Transition Status” dimension.

The expression might be as follows.

Aggregate(
 {[Transition Status].[Transferred],
 [Transition Status].[Delivered]})

Then you can use the distinct count measure “Transitions to status issues count” with this calculated member.

That allows rewriting the iteration into mathematic operations with sets - values already aggregated at database level.
You need to take the issues with proper resolution, add issues having specific transitions, and then subtract the overlapping part - issues with resolution and having these transitions.
The final expression might look as follows.

([Measures].[Issues resolved count],
 [Resolution].[Won't Do])
+
([Measures].[Transitions to status issue count],
 [Transition Status].[Completed Statuses])
-
([Measures].[Transitions to status issue count],
 [Resolution].[Won't Do],
 [Transition Status].[Completed Statuses])

This should be a dramatically faster calculation.

Still, the rest of the report context also applies. Therefore, you might check the iteration with the improved conditions against the arithmetic operations with tuples.

Regards,
Oskars / support@eazyBI.com