Simple Report from Calculated Measure

Questions & Answers
1

I have EazyBI connected to Jira.

Jira tickets have 2 string fields, imported in EazyBI as Dimensions and Measures (DIDDET and SHODET). They contain basically letters and a description “A (descr1)”, “B (descr2)”, “C (descr3)” and so on, stored as string.

I have then created a calculated measure called Slipped:

IIF(CoalesceEmpty([Issue].CurrentHierarchyMember.get('**DIDDET**'), "") > CoalesceEmpty([Issue].CurrentHierarchyMember.get('**SHODET**'), "") AND
    CoalesceEmpty([Issue].CurrentHierarchyMember.get('**SHODET**'), "") <> 'Z (No Fault)'
,1,0)

Simple saying … it should count 1 if DIDDET is alphabetically after SHODET, otherwise, 0. If SHODET is equal to Z (No Fault), Slipped should also be 0.

Adding dimension Issue on rows, and Slipped on columns, it works perfectly when member level is “Issue” and fails when the row refers to upper levels, for instance “All Issues”. I saw examples where I need to check the level = “ISSUE”, otherwise, I need to sum descendents. I read somewhere in the documentation if a measure is numeric, default aggregation is SUM. How to apply this concept?

In reality, I don´t want to specify dimension Issue in my report. I used it in my first attempt to debug if my calculated measure was working properly.

My goal is:
Rows: Dimension Time
Columns: Dimension DIDDET
Cells: the count of Issues created, split by DIDDET, counting issues with Slipped = 1. (or, better yet, summing measure Slipped)

And, of course, it should take into consideration filters defined on the Page Sections.

I tried other approaches, using Descendents, Count, but all cases I got a timeout.

Does any one have a clue on how to solve it?

The problem is kind of simple. A need to create a numeric calculated measure for an Issue (in my case, 1 or 0, but it may also be any numeric value) … No matter the combination of rows and columns and filters pages, I just want the sum of the calculated measure. Exactly the same as we have on Hours Spent.

Thanks in advance for any support.

2

It seems more promissing this calculated measure Slipped

IIF(
  [SHODET].CurrentMember.Name = 'Z (No Fault)',
  0,
  IIF(
    [DIDDET].CurrentMember.Name > [SHODET].CurrentMember.Name,
    [Measures].[Issues created],
    0
  )
)
3

It seems to be solved as


NonZero(Count(
  Filter(
    DescendantsSet(
      [Issue].CurrentMember,
      [Issue].[Issue]
    ),
    Not IsEmpty([Measures].[Issue created date]) AND
    DateBetween(
      [Measures].[Issue created date],
      [Time].CurrentHierarchyMember.StartDate,
      [Time].CurrentHierarchyMember.NextStartDate
    ) AND
    CoalesceEmpty([Issue].CurrentHierarchyMember.get('DIDDET'), "") > 
    CoalesceEmpty([Issue].CurrentHierarchyMember.get('SHODET'), "") AND
    [Issue].CurrentHierarchyMember.get('SHODET') <> 'Z (No Fault)'
    AND
    [Measures].[Issues created] > 0
  )
))
4

Hi @Sandro_Guimaraes_Bri

You’re spot on!

Here’s a bit more optimized formula:

NonZero(
  Sum(
    Filter(
      DescendantsSet([Issue].CurrentMember, [Issue].[Issue]),
      DateInPeriod(
        [Measures].[Issue created date],
        [Time].CurrentHierarchyMember
      )
    ),
    CASE WHEN
      CoalesceEmpty([Issue].CurrentHierarchyMember.get('DIDDET'), "") > 
      CoalesceEmpty([Issue].CurrentHierarchyMember.get('SHODET'), "") 
      AND
      [Issue].CurrentHierarchyMember.get('SHODET') <> 'Z (No Fault)'
    THEN
      [Measures].[Issues created]
    END
  )
)

Cheers!
Nauris