How to Join Epics and Stories to 'inherit' a field from the Epics

I’m new to EazyBI. Had a chat with @janis.plume at the Valiantys day a few months ago which convinced me of how powerful the application is :slight_smile: Right now I’m struck trying to build the following:

A chart that shows the “Theme” (custom filed) of our Stories, where the Theme can either be inherited from the Epic, or set directly at the Story level (if a Story has a Theme and and Epic, the Epic will take priority). There is only one theme per Epic or Story.

I’m a little ignorant to the EasyBI syntax, but I can outline how I would get the dataset if I were using SQL (I think) …
SELECT
CASE
WHEN S.Epic_Link is not null
Then E.Theme
Else S.Theme
End AS Theme,
S.Story_Points
FROM Story S
LEFT JOIN Epic E
ON E.Epic_ID = S.Epic_Link

I have tried creating a calculated member, but have got something wrong:
CASE
WHEN
Not IsEmpty ([Issue].[Epic Link])
THEN [Issue].CurrentHierarchy.GetLinkedMember(’[Epic Link]’).get([Issue].[Theme])
ELSE [Issue].[Theme]
END

Apologies if the question is answered elsewhere

Hi Thomas,

Thanks for the feedback on eazyBI, it was an excellent event at Valiantys.

You are quite close to the solution, just some syntax issues in your formula:

CASE
WHEN
Not IsEmpty ([Measures].[Issue Epic Link])
THEN [Issue].[Issue].GetMemberbyKey([Measures].[Issue Epic Link]).get('Theme')
ELSE [Measures].[Issue Theme]
END

Best regards,

Thank you for the reply. I have tried this but still doesn’t work – makes me think I’ve possibly misconfigured something elsewhere. (n.b. I also tried replacing your [Measures].[Issue Theme] with [Measures].[Theme] and no luck)

Here is the setup for a Story:

and likewise for an Epic:

Interesting behaviour: with the CalcTheme calculated member I created. Whenever I drag Theme back to the top row with all the other dimensions (and my chart therefore has no data), if I click CalcTheme nothing appears:

Then if I go and click on Select Members – All Themes, it loads data:
image

To me that feels like CalcTheme is not working at all; can you think of anything obviously wrong with what I am doing? My import settings appear to be correct RE Epic Link:
image

Here is the current chart export:
“cube_reports”: [ {
“name”: “IT Focus”,
“result_view”: “pie_chart”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:[“[Measures].[Story Points change]”],“members”:}]},“rows”:{“dimensions”:[{“name”:“Theme”,“selected_set”:[“[Theme].[All Themes]”,“[Theme].[CalcTheme]”],“members”:[{“depth”:0,“name”:“All Themes”,“full_name”:“[Theme].[All Themes]”,“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:true,“drill_level”:“[Theme].[Theme]”,“removed”:true}],“bookmarked_members”:}]},“pages”:{“dimensions”:[{“name”:“Sprint”,“selected_set”:[“[Sprint].[Sprint].Members”],“members”:[{“depth”:2,“name”:“ND Sprint 2018.7.2”,“full_name”:“[Sprint].[ND by Person].[ND Sprint 2018.7.2]”,“url_id”:47,“url_sub_id”:3}],“bookmarked_members”:,“current_page_members”:[“[Sprint].[ND by Person].[ND Sprint 2018.7.2]”]},{“name”:“Issue Type”,“selected_set”:[“[Issue Type].[All Issue Types]”],“members”:[{“depth”:0,“name”:“All Issue Types”,“full_name”:“[Issue Type].[All Issue Types]”,“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false}],“bookmarked_members”:,“current_page_members”:[“[Issue Type].[All Issue Types]”]}]},“options”:{“nonempty”:true},“view”:{“current”:“pie_chart”,“maximized”:false,“pie_chart”:{“swap_axes”:false,“donut”:false,“show_legend”:true,“show_labels”:true,“data_labels”:“percentage”,“relative_size”:false,“series_options”:{}}}}
} ],
“calculated_members”: [{“name”:“Issue Epic Link”,“dimension”:“Measures”,“formula”:“[Issue].CurrentHierarchyMember.get(‘Epic Link’)”,“format_string”:“”,“annotations”:{“group”:“Issue properties”}},{“name”:“CalcTheme”,“dimension”:“Theme”,“formula”:“CASE\nWHEN\nNot IsEmpty ([Measures].[Issue Epic Link])\nTHEN [Issue].[Issue].GetMemberbyKey([Measures].[Issue Epic Link]).get(‘Theme’)\nELSE [Measures].[Issue Theme]\n–ELSE [Measures].[Theme]\nEND\n\n”,“format_string”:“”}]
}

Thanks for the assistance on this. Finding EazyBi very useful so far. Fingers crossed I can resolve this one!

Hi @janis.plume

Is a subarea level possible?