Importing 2 Custom Fields with History, Combining as a Measure for a report

Questions & Answers
, ,
1

Hi, there, i’m stumped. I couldn’t seem to get this easy enough looking report to work for like forever. Now that i’m finally finished, i fear that I might have broken some rules that could bite me later. Please tell me where I went too far or wrong?

Setup: we have an issue type called “risk”. In this issue type, you can fill two custom fields (Single Select ++) called “damage” and “probability”. Risks will occasionally be updated in those fields and once no longer relevant, be closed. We also have a scripted field, which calculates a risk “score” from those two fields, which we will need to reproduce for eazyBI since scripted fields history cannot be imported to eazyBI.

My import settings for both fields look like this, and it seems the data arrives just fine in the cube.

data_type="string"
dimension="true"
seperate_table="true"
changes="true"

I defined a custom measure that seems to work fine for getting all the tickets i need properly into a timeline: (though i’m not sure if that is the preferred way or if it is even correct - just looks fine)

Cache(
  NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),
    Cache([Measures].[Transitions to]
        - [Measures].[Transitions from]
        - [Measures].[Issues closed])
  ))
  + [Measures].[Transitions to]
  - [Measures].[Transitions from]
  - [Measures].[Issues closed]
)

As this only works on each dimension, not a combined score, I combined them into Tuples without much calculation done. There are only 25 distinct possible values and 4 resulting score areas.

([damage].[catastrophic], [probability].[almost certain])+ ([damage].[catastrophic], [probability].[very likely])+ ([damage].[critical], [probability].[almost certain])

However, I did this in calculated members. All those 4 members yield a result looking exactly what I needed, but also an eazybi warning: Do not use the other dimension **damage** when defining a calculated member in **probability** . Create complex calculations in **Measures** .

And then I realized: I have no clue, how to do that as a measure. But is it important to do so?

Thank you for reading through all this. :slight_smile:

2

Hi,

We do not recommend mixing dimensions when creating calcualted members in dimensions.
Instead, you would need to create the all these intersection calculations as calculated measures.

If you had imported both single-select fields as dimensions with value changes, you could try using the “Issues history” measure with them if the aim is to calcualte the count of risks.

Try creating new calculated measure with this formula:

(
[Measures].[Issues history],
[Damage].[Catastrophic],
[Probability].[almost certain]
)
+
(
[Measures].[Issues history],
[Damage].[Catastrophic],
[Probability].[very likely]
)
+
(
[Measures].[Issues history],
[Damage].[critical],
[Probability].[almost certain]
)

That should calcualte the count ofissues for these 2 field combinations and you can use the new measure with Time dimension members in the report.

Martins / eazyBI