Hide Rows that have single value in cell

I am creating a report to surface any Story that is tagged with more than 1 sprint. I am using the measure as shown here:

[Sprint].[Sprint].getMemberNamesByKeys(
[Issue].CurrentHierarchyMember.get(‘Sprint IDs’)
)

The report returns all stories and lists the Sprint ID for each one in a row, based on my configuration. I want to hide any story that has sprint EMPTY (easy enough) and also any story that has 1 Sprint ID. This is the part I need help with. I only want to show rows where Sprint count > 1

Can you provide some guidance?
Please see screenshots.

Thanks,
Drew


Hi @drewwiseman,

Since you already have the Issue dimension on rows, you can access the issue property “Sprint IDs”.
You can look at that property as a list of references to the sprints, or you can look at it as a comma-separated list of numbers.

If you look for the issues with multiple sprints - you are looking for the lists with more than on entry. Since a coma separates multiple entries, you are actually looking for a comma in the property “Sprint IDs”.

Therefore, the expression to determine if the issue was related to multiple sprints might look as follows.

CASE WHEN
 CoalesceEmpty([Issue].CurrentHierarchyMember.Get('Sprint IDs'),'') MATCHES '.*,.*' 
THEN
--put here the actual calculation for the relevant issues 
1 
END

Since the “(no sprint)” has the ID of ‘-1’ and it is the only entry, this will also be excluded.

You might use this expression to filter rows based on measure values as described here - Create reports.
Alternatively, you might wrap other calculations within this condition to optimize the server load.

Regards,
Oskars / support@eazyBI.com

Oskars,

This was very helpful. Thank you!

Drew

Hi @oskars.laganovskis How can I sort each comma-separated value, numbered one after the other?

Hi @must1,
The possible options depend on what comma-separated values you have.

If those are Sprint IDs or references to multi-value dimension members, you might convert them into members by GetMembersByKeys(), then sort the dimension members accordingly by Order() and return the ordered list via Generate().

However, if those are general numeric figures without any reference to dimension members - you might have to add a JavaScript calculation to the custom field import settings to convert the initial string into an array of records, then sort them accordingly and return them as a string.

Please share more details about the actual setup and requirements to see a reasonable solution.

Regards,
Oskars / support@eazyBI.com

@oskars.laganovskis For example, for my Demand on line 2, I want to enumerate and list the comma-separated Defect Keys. Right now they are separated by commas and I want to do as follows. How can I do it with a formula?

Defect Key 1: ADC-9632
Defect Key 2: ADC-8745
Defect Key 3: ADC-4821

This is my Defect Keys definition: