Catching inconsistent Due Date fields

I am trying to flag issues where the Due Date of any of its Realized links are later than the Due date of the current issue. I can get a comma separated string of the Realized links with [Measures].[Issue Realized], but then how do I split the string to use each issue key as a part of a query (like maybe GetMemberByKey?)?

My plan was to Aggregate() to count how many met the condition in a Filter(), then apply conditional formatting where the result is >0.

// pseudo-code:
realizedIssues = [Measures].[Issue Realized]
Aggregate(Filter(realizedIssues, realizedIssue.[Issue due date] > [Measures].[Issue due date]))

Any tips to get this going?

Thanks!

David

You have a correct guess. You can use GetMemberByKey or GetMembersByKeys if you have several directly linked realized issues.

I would suggest addressing the dimension of the linked realized issues to access due dates of linked issues. You would like to check if you are importing issue links as property and dimension as well. You can access any issue property from linked issue dimension properties if you are using the default issue link import option.

In the example below, I have several directly linked issues. Therefore, I used GetMembersByKeys. This function will give me a set of linked issues. I retrieved linked issues from linked issue dimension and then compared any linked issue due date with an issue due date. I generated the list of linked issues with due dates after the issue due date as a result:

CASE WHEN [Issue].CurrentMember.Level.Name = "Issue" THEN
  Generate(
    Filter(
    [Realized].[Realized].GetMembersByKeys([Issue].CurrentHierarchyMember.Get("Realized")),
    DateCompare(
      [Issue].CurrentHierarchyMember.GetDate("Due date"),
      [Realized].CurrentHierarchyMember.GetDate("Due date")
    ) < 0),
   [Realized].CurrentMember.Name , ",")
END

Here is my report example:

@daina.tupule Awesome, this seems to do it (for one level of Realizes)!

Now the trick is teaching me to fish… :slight_smile:

Decomposing this to one piece of it… this seems to be the way to “separate” a set (in this case, the set returned by GetMembersByKeys):

Generate(
  [Realized].[Realized].GetMembersByKeys([Issue].CurrentHierarchyMember.Get("Realized")),
  [Realized].CurrentMember.Name , 
  " : "
)

This produces
Issue1 : Issue2 : Issue3
(where Issue1, Issue2, and Issue3 all Realize the issue in the current row)

So some questions:

  1. What is [Realized].[Realized]? The first Realized seems to be a Dimension and the second is a Level? What is that double [Realized] vs something like simply:
    Generate(
    [Issue].CurrentHierarchyMember.Get(“Realized”),
    [Realized].CurrentMember.Name ,
    " : "
    )
    ([Issue].CurrentHierarchyMember.Get(“Realized”) seems to be a , which EazyBI doesn’t accept as the first argument of Generate())

  2. The second [Realized] there doesn’t show up in the autocompleter - is that expected?

  3. I don’t understand the second Generate() argument of “[Realized].CurrentMember.Name” - is that saying "Generate is iterating over the first argument (a set, call each member ‘s’) and doing ‘s’.Name? What’s the CurrentMember doing?

I think if you could explain this part (which I’ve failed to understand after reading the documentation many times) that would really help me get the core ideas of these functions.

Thanks!

The formula I shared with you retrieves linked issues in Realized dimension on Realized level. Filters out the ones that has due date after issue due date and will give you comma-separated list of linked issues with due dates after issue due date.

[Realized].[Realized] represents level [Realized] in dimension [Realized]. The first one stands for dimension and another one for level.

[Realized].[Realized].GetMembersByKeys(<…>) :
GetMembersByKeys works with a particular level of the dimension and retrieves a set of members in this dimension on a particular level. You would like to use comma separated string with member keys member as a parameter for this function. In this case comma-separated list of issue keys. This function will give you a set of members as a result. In a set of members, you can access members and properties for each member ([Realized].CurrentMember or ([Realized].CurrentHierarchyMember), in this case, Due date of Realized issue and then name (issue key).

[Issue].CurrentHierarchyMember.Get(“Realized”) is issue property and will give you String as a result (comma-separated list of issue keys)

Generates function works on a set of members (therefore you can use issue property there) and will transfer it as a String in result using a property of the members in the set ([Realized].CurrentMember.Name will give you linked issue key).

Daina / support@eazybi.com