Hi, I’m fairly new to MDX as a whole, and I’m struggling with coming up with the solution for a particular scenario. In this case I want to pull a report of Story Points Closed across almost all Epics, minus a few particular ones. In the case of JQL I know I could just say (“Epic Link” is empty OR “Epic Link” not in (ABC-111, ABC-112)). However, in EazyBI how would I accomplish this?
I’ve tried creating a Calculated Member under the Epic Link dimension, but the most I can get is for it to return back “true” in the Story Points Closed column instead of the sum of the story points.
Here’s the Calculated Member formula I’m trying:
isEmpty([Measures].[Issue Epic Link])
OR [Measures].[Issue Epic Link] <> “ABC-111”
OR [Measures].[Issue Epic Link] <> “ABC-112”
There are at least 3 options for how to achieve this, and each has some pros and cons.
I will demonstrate each option with example data from my test environment and aim to exclude (no epic) and DA-387.
(1) If you want to see epic link dimension in the rows and need to remove some epics only for few reports, click on the row and use the “Remove” option. This is very quick. The problem with removed members is that you must do this manually in each report and you can not easily see which members are removed (you can see it in the report definition).
(2) If you want to have Epic Link dimension members in the rows, you can also create calculated member in Epic Link dimension using Filter() function. It can be expanded, but it can be very slow if you have many epic links:
(3) If you do not need Epic Links in the rows, then you can create the following member, which is faster but can not be expanded (therefore best to select it in page filters). This member will impact all report measures and while it will work ok with most predefined measures, it might create unexpected results with more complex measures (most will work, but need to test to be sure):
