Trying to track DDE in eazyBI

Questions & Answers
, ,
1

Hello again,

I’m trying to get a count/sum of all issues that match the criteria of being logged prior to release.

Thanks to some help on another thread: Want to look up the release date attached to the affects version dimension with the value from a custom measure - #2 by nauris.malitis I was able to pull the release date I needed for comparing the created date.

I made a custom measure that has the release date of the lowest affects version [LowestAVLiveDate]
When I use my formula:

DateCompare([Measures].[Issue closed date],[Measures].[LowestAVLiveDate]) = -1

It successfully identifies which issues which logged on an individual issue basis. The difficulty I’m having is when trying to return all issues that have a true value on a higher level report.

I’ve tried a few things to get this to work:

NonZero( 
  Count(
    Filter(
       Descendants([Affects Version].CurrentMember,[Affects Version].[Version]),
                 DateCompare([Measures].[Issue closed date],[Measures].[LowestAVLiveDate]) = -1
    )
  )
)

I’ve also tried using aggerate/sum in place of nonzero(count but they never return results it seems.

I’ve tried messing with the formatting to no avail.

Additional info in case it’s relevant:
On my rows I’m filtering by issue type/project with prebuilt members as well as a custom issue calculated member that filters affects version based on ID(this was to avoid picking up issues that have a lower affects version):

Sum(
Filter(
  [Issue].[Issue].Members, 
  --ID starts with the following version ID
  [Issue].CurrentHierarchyMember.Item(0).get('Affects version IDs') MATCHES '^200.*'
  --Does not contain lower versions
  AND NOT
  --version 2.30
  [Issue].CurrentHierarchyMember.Item(0).get('Affects version IDs') MATCHES '.*188.*'
  AND NOT
  --version 2.20
  [Issue].CurrentHierarchyMember.Item(0).get('Affects version IDs') MATCHES '.*187.*'
    AND NOT
  --version 2.10
  [Issue].CurrentHierarchyMember.Item(0).get('Affects version IDs') MATCHES '.*186.*'
)
)```

Thanks for any insights you might have!
2

Hi @Davo

Instead of iterating through the Affects Version members, please try iterating through the Issue members with a formula like this:

NonZero( 
  Sum(
    Filter(
       Descendants([Issue].CurrentMember,[Issue].[Issue]),
       DateCompare(
         [Measures].[Issue closed date],
         [Measures].[LowestAVLiveDate]
       ) = -1
    ),
    CASE WHEN
    [Measures].[Issues closed] > 0
    THEN
    1
    END
  )
)

The CASE statement is necessary to have a real measure in this formula, so that it would be mapped to the Closing date selected in your report.

About the second formula- is this from a member in the Issue dimension?
This is not the recommended approach to filter the issues, a better way would be to define a member in the Affects Version dimension with a formula like this:

Aggregate(
  Filter(
    [Affects Version.By name].[Name].Members,
    [Affects Version.By name].CurrentMember.Name MATCHES '^200.*'
    AND NOT
    [Affects Version.By name].CurrentMember.Name MATCHES '.*188.*|.*187.*|.*186.*'
  )
)

Let me know if this works as expected!
​Best regards,
​Nauris

3

Hi Nauris

The first calculation worked when i removed the Issues dimension from my Rows. Thank you!

Unfortunately the Affects version member you suggested doesn’t meet my needs as I’m trying to only show issues that have the lowest affects version.

The member I had made on the Issues dimension was able to do this to give more accurate numbers.

So I am back to trying to solve for only showing issues based on their lowest affects version.