Generate Set from a string

Hi,

I have a problem with one of my reports. What I want to do is to count Fix versions by month, but with a slight catch. We have multiple releases that have the same release number as a part of their name, like this:

  • AN 5.1.1 New links
  • AN 5.1.1 Bugfixes
  • AN 5.1.1 New feed

    When we create our report, those Fix versions are counted separately - which is expected, but we would like to count all that have the same number as a single release.

What we tried is to create a custom calculated measure:
StrToSet(
Generate(
Filter(
Descendants([Fix Version].CurrentMember, [Fix Version].[Version]),
DateInPeriod(
[Measures].[Version release date],
[Time].CurrentHierarchyMember)
AND
[Fix Version].CurrentMember.Get(“Status”) <> “Unreleased”
AND
[Fix Version].CurrentMember.Name MATCHES “AN .*”
AND
([Measures].[Issues created], [Time].CurrentHierarchy.DefaultMember) > 0
)
,
ExtractString([Fix Version].CurrentMember.Name, ‘\s+([^\s]+)’, 1),
', ’
)
, WHAT TO PUT HERE???)

The idea is that we can get all Fix versions in a period, extract the version part of the string from the Fix version name, put that back as a set, remove duplicates and count remaining members.

But we can’t get StrToSet to work and we can’t find any docs around this - any help or better suggestion on how we could achieve this?

Kind regards,
Bruno

Hi @Bruno_Prebeg,

Your idea about StrToSet(…) is great, but unfortunately, it will not work - a set can be constructed only if such a member’s name matches the string exactly. So as I assume, there are no versions with just the numeric part of the name; this will give an empty set.

For the future though, the second argument would be the Fix Version hierarchy from which you wish to construct the set, e.g.

NonZero(Count(Distinct(
StrToSet(Generate(
...
), [Fix Version.By name]))
))

I thought long and a lot about your request, and the idea I came up with would be to order the versions by the version number and compare if such number is already added in the set - as they are ordered, compare with the previous version number. Here is the resulting MDX with some inline comments

Count(
Filter(
  Order( -- go thorough set of filtered and ordered versions
    Filter(
      Descendants([Fix Version].CurrentMember, [Fix Version].[Version]),
      DateInPeriod(
        [Measures].[Version release date],
        [Time].CurrentHierarchyMember) 
      AND
		[Fix Version].CurrentMember.Get('Status') <> 'Unreleased'
	  AND
		[Fix Version].CurrentMember.Name MATCHES 'AN .*'
      AND
      ([Measures].[Issues created], [Time].CurrentHierarchy.DefaultMember) > 0
    ),
    ExtractString([Fix Version].CurrentHierarchyMember.Name, '\d+\.+\d', 0), BASC
  ),
  -- Compare version name with prev member name
  StrComp(
    ExtractString([Fix Version].CurrentHierarchyMember.Name, '\d+\.+\d', 0), -- The part of current member name
    CoalesceEmpty(
      ExtractString(
        Order(
          Filter([Fix Version].[Version].Members, -- filter and order versions again to get the set
            DateInPeriod(
              [Measures].[Version release date],
              [Time].CurrentHierarchyMember) 
		      AND
				[Fix Version].CurrentMember.Get('Status') <> 'Unreleased'
			  AND
				[Fix Version].CurrentMember.Name MATCHES 'AN .*'
		      AND
            ([Measures].[Issues created], [Time].CurrentHierarchy.DefaultMember) > 0
          ),
          ExtractString([Fix Version].CurrentHierarchyMember.Name, '\d+\.+\d', 0), BASC
        ).Item( -- from this set we get the previous item
          Rank( -- by finding the rank of current member
            [Fix Version].CurrentMember, 
            Order(-- again in the same ordered set
              Filter([Fix Version].[Version].Members,
                DateInPeriod(
                  [Measures].[Version release date],
                  [Time].CurrentHierarchyMember)      
			      AND
					[Fix Version].CurrentMember.Get('Status') <> 'Unreleased'
				  AND
					[Fix Version].CurrentMember.Name MATCHES 'AN .*'
			      AND
                ([Measures].[Issues created], [Time].CurrentHierarchy.DefaultMember) > 0
              ),
            ExtractString([Fix Version].CurrentHierarchyMember.Name, '\d+\.+\d', 0), BASC
            )
          )-2
        ).Name, '\d+\.+\d', 0
      ),""
    )
  ) <> 0) -- add the version to set only if the name does not match with the previous
)

This was a nice MDX challenge, Thank you for that! :slightly_smiling_face:

Let me know if I missed something and you have some further questions!
Lauma / support@eazybi.com