Hi @PJohansson,
Welcome to the eazyBI community!
If you have distinct issues on the report rows, the figure you want to see might be described as follows.
If the issue is created within the current month, show me if the date field has value in any future or past date.
You need to reset the Time dimension when checking if the issue has the specific date field filled with any future or past date.
You might reset the dimension with the .DefaultMember reference. You might put in reference to the CurrentHierarchy just in case you might use it with weekly or other hierarchy on the Time dimension.
So, the expression to find if the date field is filled at all might be as follows.
([Measures].[Issues with Incident started],
[Time].CurrentHierarchy.DefaultMember)
This would return value 1 if the field has any value or blank if the value is missing.
I see that you want to see an explicit zero for blank results.
You might then wrap the expression in the CoalesceEmpty() function to set the value for the empty result.
CoalesceEmpty(
([Measures].[Issues with Incident started],
[Time].CurrentHierarchy.DefaultMember),
--value for empty
0)
An alternative version might be as follows.
IIF(
Isempty([Issue].CurrentHierarchyMember.Get("Incident started")),
0,
1)
However, this will show value for any issue. Since you only want to show values for issues created within the selected period - you need to wrap this into a condition only to return value if the issue creation relates to the report context.
The expression might then be as follows.
CASE WHEN
[Measures].[Issues created]>0
THEN
CoalesceEmpty(
([Measures].[Issues with Incident started],
--reset the Time dimension
[Time].CurrentHierarchy.DefaultMember),
--value for empty
0)
END
This works great on the level of individual issues.
However, on the level of the Project - it will display the number of issues with the custom date field filled for all issues in the project.
In that case, you need to create a set of issues within the project, filter out the issues that are relevant to the context (created date), and then sum up issues with filled custom date fields.
You might create the set of issues within the project in the Issue dimension by using the Descendants() function.
Descendants(
--addressing displayed Issue dimension entity
[Issue].CurrentHierarchyMember,
--define the level for the members in the set
[Issue].[Issue])
Then, you might filter this issue set by condition if the issue relates to the context.
Filter(
--set of issues within project
Descendants(
--addressing displayed Issue dimension entity
[Issue].CurrentHierarchyMember,
--define the level for the members in the set
[Issue].[Issue]
),
--filter condition - relates to context
[Measures].[Issues created]>0
)
Now, you have the set of relevant issues related to the current month.
The remaining step is to sum up the number of issues with the date fields filled.
Sum(
Filter(
--set of issues within project
Descendants(
--addressing displayed Issue dimension entity
[Issue].CurrentHierarchyMember,
--define the level for the members in the set
[Issue].[Issue]
),
--filter condition - relates to context
[Measures].[Issues created]>0
),
--numeric value for sum - number of issues with date filled
([Measures].[Issues with Incident started],
[Time].CurrentHierarchy.DefaultMember)
)
The good thing is that the Descendants() function would return the issue itself when executed on the issue level. So, the same approach works both for project and individual issue.
Now, we can wrap the result in CoalesceEmpty() to return zero for blanks and also in conditional check - so that we only iterate through projects that relate to the context.
CASE WHEN
-- Condition to check if the row should be displayed
[Measures].[Issues created] > 0
THEN
CoalesceEmpty(
-- Number of issues with custom date field populated from the issues created within the current context
Sum(
Filter(
-- Set of issues within the project
Descendants(
-- Addressing displayed Issue dimension entity
[Issue].CurrentHierarchyMember,
-- Define the level for the members in the set
[Issue].[Issue]
),
-- Filter condition - relates to context
[Measures].[Issues created] > 0
),
-- Numeric value for sum - number of issues with a date filled
([Measures].[Issues with Incident started],
[Time].CurrentHierarchy.DefaultMember)
),
-- Value for blank
0
)
END
The alternative based on the issue property instead of measure might work slightly faster.
CASE WHEN
-- Condition to check if the row should be displayed
[Measures].[Issues created] > 0
THEN
CoalesceEmpty(
-- Number of issues with custom date field populated from the issues created within the current context
Sum(
Filter(
-- Set of issues within the project
Descendants(
-- Addressing displayed Issue dimension entity
[Issue].CurrentHierarchyMember,
-- Define the level for the members in the set
[Issue].[Issue]
),
-- Filter condition - relates to context
[Measures].[Issues created] > 0
--date field populated
AND
NOT IsEmpty([Issue].CurrentHierarchyMember.Get("Incident started"))
),
-- Numeric value for sum
1
),
-- Value for blank
0
)
END
This should work fine in the absolute majority of instances. However, it might get slow on instances with a very high number of issues.
In those cases, you might consider creating additional JavaScript-calculated customfield dimensions that precalculate if the custom date field is populated already during the data import.
Please let me know if the above expression takes more than 10 seconds to produce a report or if you use a different report setup - like removing the Issue dimension from the report rows.
Regards,
Oskars / support@eazybi.com
