I have a specific use case, but believe this is a useful item for myriad cases.
I have the below table of initiatives, grouped by components. I am pulling in the custom field “Project Status RYG” to show if those initiatives are Red, Yellow, or Green as updated on their JIRA boards. I would like to use a conditionally populated calculated member using the following pseudo code, based on the workflow status of the initiatives in JIRA:
IF “Project Status RYG” in (Red, Yellow, Green) AND Status != Closed THEN “Project Status RYG” = “Project Status RYG”
IF status = Closed THEN “Project Status RYG” = “Complete”
The idea here is that when a user moves the Initiative to closed in JIRA, it will reflect as completed in the R/Y/G view, rather than appearing to still be in progress, and without adding additional columns to the view.
Thanks so much, I think this sort of “change this, based on that” would be useful for all sorts of use cases when distilling information for executive leadership.
If I understand the situation correctly, you can create a new calculated measure similar to the following:
IIF(
[Measures].[Issue resolution] = “Completed” OR
[Measures].[Issue resolution] = “Cancelled”,
[Measures].[Issue resolution],
[Measures].[Issue status])
Here the value of one measure is driven from the contents of two different measures.
For your case, it would be something like:
IIF(
[Measures].[Issue status] = “Closed”,
“Complete”,
[Measures].[Project Status RYG])
>>The idea here is that when a user moves the Initiative to closed in JIRA, it will reflect as completed in the R/Y/G view, rather than appearing to still be in progress, and without adding additional columns to the view.
If the Initiative movement in JIRA is performed via workflow transition, may be it is better idea to set your CustomField value in JIRA to proper value and then your dashboard will contain correct values.
If this is not possible, I would consider writing a custom javascript code that performs the required data transformation during the data import process. The reason for that is better performance - you do calculation only once, during import, not every time the report is requested. This becomes especially important when you are dealing with large data sets.
I would agree with that approach, but am working against two constraints: 1) Our admins aren’t accepting new JIRA custom fields right now, and the RYG only allows for those options or None. 2) Even if they did, the reality of my users is they are less likely to update multiple fields reliably, they only want to push to closed.
That said - great feedback and I will push that as our target state as we mature this thing, and thanks all for the help.
