Dear community,
I want to sum up all hours spent on tickets, where the SAP No. is “0”. The SAP no. is as property imported. How can I achieve this?
Thanks in advance and best regards-,
Nicole
If the SAP number is imported as a property, you may need to create a calculated measure that iterates through issues and checks if the issue has 0 in this property and then sums up the logged hours from such issues. The formula would be the following:
Sum(
Filter(
Descendants(
[Issue].CurrentMember, [Issue].[Issue]
),
[Measures].[Issue SUP number property name] = "0"
),
[Measures].[Hours spent])
Use the correct property name; check if it is a string or numerical value, and use “0” or 0 in comparison accordingly.
If you have a large set of issues, this calculation might be slow!
If the given calculation is too slow or you need to filter such issues frequently, you may want to consider another solution: invent a new calculated custom field. During data import, you would use JavaScript to check the issue SAP number. Based on that, the code would return values “Zero” and “Non-zero” in this new field (you may use more business-related values). Then, you would import this field as a dimension.
Finally, you would filter the report by this dimension, value “Zero”, to get data only from issues with “0” in SUP no. property or use this member “Zero” in a tuple with the measure “Hours spent.”
Read more about inventing a calculated JavaScript custom field: JavaScript calculated custom fields
Best,
IlzeLA, support@eazybi.com