Generate Set from a string

Bruno_Prebeg · January 11, 2021, 10:39pm

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

lauma.cirule · February 12, 2021, 12:01pm

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!

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

Ofer_Cohen · January 26, 2024, 8:56am

I was trying to use the above method to generate a list of Teams (based on a custom field ‘Team Name’) that will show on the epic all the teams without having duplicating team names.
I get error message:Failed to execute query. Error message:
PLEASE HELP
This is what I used:
CASE WHEN [Measures].[Issue type] = [Issue Type].[Epic].Name
THEN
Generate(
Filter(
Order( – go thorough set of filtered and ordered teams
Filter(
ChildrenSet([Issue].CurrentHierarchyMember),
[Issue].CurrentHierarchyMember.GetString(‘Team Name’) <>
[Issue].CurrentHierarchyMember.GetString(‘Team Name of Epic’)
AND
([Measures].[Issue status] <> “Done” AND
[Measures].[Issue status] <> “Cancelled” )

  ), [Measures].[Issue Team Name]

),
– Compare team name with prev member name
StrComp(
[issue].CurrentHierarchyMember.GetString(‘Team Name’), – The part of current member
CoalesceEmpty(
Order( – go thorough set of filtered and ordered teams
Filter(
ChildrenSet([Issue].CurrentHierarchyMember),
[Issue].CurrentHierarchyMember.GetString(‘Team Name’) <>
[Issue].CurrentHierarchyMember.GetString(‘Team Name of Epic’)
AND
([Measures].[Issue status] <> “Done” AND
[Measures].[Issue status] <> “Cancelled” )

  ), [Measures].[Issue Team Name]
    ).Item( -- from this set we get the previous item
      Rank( -- by finding the rank of current member
        [issue].CurrentHierarchyMember, 
        Order( -- go thorough set of filtered and ordered teams
            Filter(
              ChildrenSet([Issue].CurrentHierarchyMember),
                [Issue].CurrentHierarchyMember.GetString('Team Name') <> 
                [Issue].CurrentHierarchyMember.GetString('Team Name of Epic') 
              AND
               ([Measures].[Issue status] <> "Done" AND 
                [Measures].[Issue status] <> "Cancelled" )

              ), [Measures].[Issue Team Name]
            )
      )-2
    ).name
)

) <> 0), – add the team to set only if the name does not match with the previous
[Issue].CurrentHierarchyMember.GetString(‘Team Name’), ', ’
)
END

lauma.cirule · January 26, 2024, 12:34pm

Hi @Ofer_Cohen,

Could you please elaborate on what is the expected result of your formula? It’s been a while since I looked at this, and it’s hard to catch an error without knowing the report’s context. And maybe, in your case, a simpler formula would be a better choice.
You can reply here or send your report details and expected results to support@eazybi.com, where my colleagues and I will gladly assist you!

Lauma / support@eazybi.com

Ofer_Cohen · January 28, 2024, 10:02am

@lauma.cirule
I have a custom field for each issue that is the owning “Team Name”. For each Epic I want to calculate a string that contains all the stories Team Names without duplicates (with a filter on the stories for all not Done stories).

If Epic 1 have these stories:

St1 assigned to Team1

St2 assigned to Team2

St3 assigned to Team2 also.

The result on the epic will be:

“Team1 , Team2”.

Without the duplication of the string “Team2”.

Thanks

Ofer.

lauma.cirule · January 29, 2024, 1:38pm

Hi @Ofer_Cohen ,

In this case, I would suggest a slightly simpler formula using the GetMembersByKeys function. This assumes that the “Team name” is imported as a dimension and you have the Issue dimension, Epic hierarchy on rows:

CASE WHEN ([Measures].[Issues created], [Issue Type].[Epic]) > 0 -- only for Epic issues
THEN
Generate(
  Distinct(Except( -- find distinct team dimension members
    [Team Name].[Team Name].getMembersByKeys(
      Generate( -- from a list of current epic children stories
        Filter(
          [Issue].CurrentHierarchyMember.Children,
          ([Measures].[Issues created], [Issue Type].[Story]) > 0
        ),
        [Issue].CurrentHierarchyMember.getstring('Team Name'), -- get the story teams
          ","
      )
    ), [Team Name].[(none)] -- do not include (except) none member
  )),
  [Team Name].CurrentMember.name,", " -- generate a list of Team member names separated by comma
)
END

Let me know if that helped or if you have further questions!
Lauma / support@eazybi.com

Ofer_Cohen · January 29, 2024, 3:14pm

This looks Good. I need the following modifications:

Include all issue types under the Epic (except for sub-task).
Do not include “Team Name” that matches the “Team Name” of the Epic.
Include only children (Story, Task,…) whose status category is not “Done”
Order the list Alphabetically by Team Name (nice to have)

Could you PLEASE help me do that (I tried and failed )

Thanks!

Ofer

lauma.cirule · January 29, 2024, 4:16pm

Hi @Ofer_Cohen ,

Sure, I’m happy it was helpful! Here are the additional modifications.

In the Status dimension Category hierarchy, create a calculated measure that would group all Status Categories that are not done. Please note that next to the name of this calculated member, you must choose the hierarchy. I have named this calculated member Not Done; this is important as it is used in the formula afterward:

Aggregate(Except(
  [Status.Category].[Category].Members,
  [Status.Category].[Done]
))

And here is the updated formula with comments on what I changed:

CASE WHEN ([Measures].[Issues created], [Issue Type].[Epic]) > 0
THEN
Generate(
  Order(Distinct(Filter( -- add the ordering of filtered Team names
    [Team Name].[Team Name].getMembersByKeys(
      Generate(
        Filter( -- when we filter the children of the epic
          [Issue].CurrentHierarchyMember.Children, 
          (
            [Measures].[Issues created], 
            [Issue Type.By type].[Standard], -- 1. we take the standard issue types instead of only Story
            [Status.Category].[Not Done] -- 3. and look at those that are in the Status categories aggregated in the previous step
          ) > 0
        ),
        [Issue].CurrentHierarchyMember.getString('Team Name'),
          ","
      )
    ), NOT [Team Name].CurrentHierarchyMember IS [Team Name].[(none)] AND -- we make sure the filtered name is not (none)
       NOT [Team Name].CurrentHierarchyMember.Name MATCHES [Issue].CurrentHierarchyMember.Get('Team Name') -- 2. as well as it does not match the name of the epic Team name
  )), [Team Name].CurrentHierarchyMember.Name ), -- 4. order by Team name
  [Team Name].CurrentMember.name,", "
)
END

Lauma / support@eazybi.com

Ofer_Cohen · January 29, 2024, 7:04pm

This is amazing!
Works Perfectly!
Thank you so much, I spent hours over hours trying to achieve that.

Ofer

Ofer_Cohen · January 29, 2024, 7:37pm

@lauma.cirule
One more question on the same topic…
I have 3 different instances.
It works on 2.
It does not work on the other one. All of them are on the cloud and have the same version of eazyBI.
The outer GetMembersByKeys is probably the cause.
When I use the inner generate I get the names (duplicated, un-ordered).
Any idea why it does not work on some Jira Instances?

lauma.cirule · January 30, 2024, 7:53am

MDX can be challenging when not practiced regularly like any scripting or programming language. We’re here to assist, and I’m happy I could help in your case!

Could you send your Jira Cloud site and the account names to support@eazybi.com and the report name in each account where it works and where it doesn’t? I’ll take a look; I imagine, most likely, there are some configuration changes in the account or the report itself, as the functions should work in the same way.

Lauma / support@eazybi.com

Ofer_Cohen · January 30, 2024, 8:36am

I found the reason. When the Jira Cloud instance is using the “Team” field from Advanced Roadmap the calculated measure does not work.
@lauma.cirule
In the Advanced settings I have this : (10001 is the custom field of advanced roadmap):

#Define Team Name
[jira.customfield_10001] name = “Team Name”

My reports always use “Team Name”

The same calculated member (and report) works on Jira Data Center with the Advanced Roadmap Team field. The only issue is on Jira Cloud.

Ofer

lauma.cirule · January 30, 2024, 9:37am

@Ofer_Cohen Thanks for the additional details! Indeed, the difference is how the Advanced roadmap Team dimension is created in the Cloud. Advanced Roadmaps Team in the Cloud does not have the Team name as the dimension member key. You can see the dimension properties with AllProperties function.

So here is a once again modified formula that goes through all Team members and finds the matching team by name (not getting them by key as we did previously):

CASE WHEN ([Measures].[Issues created], [Issue Type].[Epic]) > 0
THEN
Generate(
  Order(
    Filter(
      Descendants([Team Name].CurrentMember, [Team Name].[Team Name]), -- go through all Team Name dimension members at Team Name level
      [Team Name].CurrentHierarchyMember.Name MATCHES -- Make sure that the Team Name matches 
      Generate(
        Filter(
          [Issue].CurrentHierarchyMember.Children, 
          (
            [Measures].[Issues created], 
            [Issue Type.By type].[Standard], 
            [Status.Category].[Not Done] 
          ) > 0
        ),
        [Issue].CurrentHierarchyMember.getString('Team Name'), "," -- any of the filtered children names
      ) AND
      NOT [Team Name].CurrentHierarchyMember.Name MATCHES [Issue].CurrentHierarchyMember.Get('Team Name') -- and does not match the name of the epic Team Name
    ), [Team Name].CurrentHierarchyMember.Name ), 
  [Team Name].CurrentMember.name,", "
)
END

Lauma / support@eazybi.com

Ofer_Cohen · January 30, 2024, 10:35am

@lauma.cirule
Thanks for the quick Reply!
I confirm that this works on the Advanced Roadmap Team field
Is there a way to have one calculated measure that will work for both cases (my own custom field or usage of the predefined Team field)?
I am using the same report on multiple instances…

Thanks.

Ofer_Cohen · January 30, 2024, 10:49am

@lauma.cirule
After further testing, the Advanced Roadmap Team calculated measure does not work in the edge case that the Epic is ‘(none)’ but the children do have the field Team Name with values.

Ofer

lauma.cirule · January 30, 2024, 2:26pm

@Ofer_Cohen

Here is another version of the formula, this time without the CASE check making sure that the issue on the row is Epic, so also (none) would be taken into consideration. Also, this should work with Cloud and Server Team custom fields:

Generate(
  Order(
    Filter(
      Descendants([Team Name].CurrentMember, [Team Name].[Team Name]), -- go through all Team Name dimension members at Team Name level
      Generate( -- generate a string with
        Filter(
          [Issue].CurrentHierarchyMember.Children, 
          (
            [Measures].[Issues created], 
            [Issue Type.By type].[Standard], 
            [Status.Category].[Not Done] 
          ) > 0
        ),
        CoalesceEmpty([Issue].CurrentHierarchyMember.getString('Team Name'), ""), "," -- any of the filtered children Team Names
      ) MATCHES ".*"||[Team Name].CurrentHierarchyMember.Name||".*" AND -- find if current team name is within the string
      NOT [Team Name].CurrentHierarchyMember IS [Team Name].[(none)] AND -- the current team name is not (none)
      NOT [Team Name].CurrentHierarchyMember.Name MATCHES CoalesceEmpty([Issue].CurrentHierarchyMember.getString('Team Name'), "") -- and does not match the name of the epic Team Name
    ), [Team Name].CurrentHierarchyMember.Name ), 
  [Team Name].CurrentMember.name,", "
)

Lauma / support@eazybi.com

Ofer_Cohen · January 31, 2024, 8:42am

@lauma.cirule
One last request about this:
I want to show all the team names under the Epic (regardless of the filter of the report), So:
Epic 1: Team1, Release1
->Story1: Team1, Release1
->Story2: Team2, Release2
->Story3: Team3, Release3

If the report filter is on “Release1”
I want that this calculated Measure (On the Epic Level only) to show:
“Team2, Team3”

I changed the ==> “[Issue].CurrentHierarchyMember.Children”
to ==> “ChildrenSet([Issue].CurrentHierarchyMember)”

But it did not work.

How can I achieve this?

Thanks,
Ofer.

lauma.cirule · February 1, 2024, 3:18pm

Hi @Ofer_Cohen ,

Yes, that’s reasonable. You can achieve this with the DefaultContext function that ignores all other filters for the children but the ones that we have chosen:

Generate(
  Order(
    Filter(
      Descendants([Team Name].CurrentMember, [Team Name].[Team Name]), -- go through all Team Name dimension members at Team Name level
      Generate( -- generate a string with
        Filter(
          [Issue].CurrentHierarchyMember.Children, 
          DefaultContext(( -- ignore other children issue context than the one specified in the tuple
            [Measures].[Issues created], 
            [Issue].CurrentHierarchyMember,
            [Issue Type.By type].[Standard], 
            [Status.Category].[Not Done] 
          )) > 0
        ),
        CoalesceEmpty([Issue].CurrentHierarchyMember.getString('Team Name'), ""), "," -- any of the filtered children Team Names
      ) MATCHES ".*"||[Team Name].CurrentHierarchyMember.Name||".*" AND -- find if current team name is within the string
      NOT [Team Name].CurrentHierarchyMember IS [Team Name].[(none)] AND -- the current team name is not (none)
      NOT [Team Name].CurrentHierarchyMember.Name MATCHES CoalesceEmpty([Issue].CurrentHierarchyMember.getString('Team Name'), "") -- and does not match the name of the epic Team Name
    ), [Team Name].CurrentHierarchyMember.Name ), 
  [Team Name].CurrentMember.name,", "
)

Lauma / support@eazybi.com

Ofer_Cohen · February 3, 2024, 5:06pm

@lauma.cirule

Perfect!
It’s just what I wanted.
I was not aware of the DefaultContext option