EazyBI - Transition from Status A to Status B - under condition

[Ale]

Hi guys,

I hope you can support me with this one: I have a list of issues that may transition both from Status A --> Status B --> Status C and from Status C --> Status B --> Status A.

I have a table in which I want to show the date in which my issues have transitioned from Status A to Status B.

At the moment, I'm doing it by creating a new calculate measure called "Transition to Status B", that looks like this:

([Measures].[Transition to status last date], [Transition Status].[Status B])

The problem with this formula is that it shows the date in which the issue has transitioned to Status B, even if after this transition, the issue has transitioned back to Status A.

What I want to obtain is to have the date shown only if:

- The transition from Status A to Status B has happened, AND

- The issue is currently in Status B (or following. e.g. Status C)

I want the cell to be empty in case:

- The transition from Status A to Status B has never happened, OR

- The transition has happened AND the issue is in Status A.

How can I correct my formula to make it work properly?

Thanks for you help ;)

[Zane eazyBI Support]

Hi @Ale

For your calculation, you might want to use the dimension "Tranistion" that covers both statuses of transition, from and to. this way, you can find issues that had a particular transition [Transition].[Status A = > Status B].

On the other hand, to filter issues by their current status (Status B or Status C), you might use the dimension "Status".

1) In the "Status" dimension, create a new calculated member "B and C" to group statuses in which issue should be, Status B and Status C.

Aggregate({
  [Status].[Status B],
  [Status].[Status C]
})

More details on calculated members in dimensions are here: https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-measures-and-members/calculated-members-in-other-dimensions

2) In Measures, you can create a calculated measure showing the last date when the issue transitioned from Status A to Status B and is currently in Status B or Status C. For the calculation, use a tuple like this:

([Measures].[Transition to status last date],
[Transition].[Status A => Status B],
[Status].[B and C])

More details on calculated measures and a tuple construction are here: https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-measures-and-members/calculated-measures

Best,

Zane / Support@eazyBI.com

[Ale]

Hi Zane,

thanks a lot!

Meanwhile I've solved it by using the following formula:

IIf(
[Measures].[Issue status] = "Status B" 
OR 
[Measures].[Issue status] = "Status C",
([Measures].[Transition to status last date], [Transition Status].[Status B]),
NULL
)

 I think the result is the same, even if your solution looks a bit more straightforward to me. I'll definitely try it.

Thanks & regards