Help on existing mdx measure

Questions & Answers
1

I am using the below measure to get the issues Due in the report. I am using DE - Completion Due Date (F2C) as a date field in jira.

CASE WHEN
 [Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember
THEN
  SUM(
    PreviousPeriods(
      [Time].CurrentHierarchy.Levels("Day").CurrentDateMember),
    [Measures].[Issues with DE - Completion Due Date (F2C)]
    
  )
WHEN
  DateAfterPeriodEnd("Today", [Time].CurrentHierarchyMember)
  OR
  DateInPeriod("Today", [Time].CurrentHierarchyMember)
THEN 
  SUM(
    PreviousPeriods(
      [Time].CurrentHierarchyMember),
    [Measures].[Issues with DE - Completion Due Date (F2C)]
    )
END

Now, can you help me to create a measure where I want get the issues in Due where I have another custom field called Country Relevance when the values are DE and US.
I know we can achieve this by adding the Country Relevance in the pages and filtering by DE and US.
But instead of using that as a filter I want to create an measure where I can filter issues due in Country Relevance DE and US.
Note : I am using Time dimension in the rows.

2

Hi @Amzad,

We had a support conversation regarding this via email. I will share my suggestion for everyone to see:

I recommend defining a new calculated member in the Country Review DE dimension to aggregate the desired members. After that, you can reference this calculated member in your formula. To create the calculated member in the Country Review DE dimension, follow the steps outlined here - Calculated members in other dimensions. The formula could look similar to the one below:

Aggregate({ 
  [Country Review DE].[(none)],
  [Country Review DE].[In Progress], 
  [Country Review DE].[Planned] 
})

After that, replace the measure “Issues with DE - Completion Due Date (F2C)” with a tuple of it and the Country Review DE dimension calculated member in your calculated measure “Delay”. The formula could look similar to the one below:

 CASE WHEN
  [Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember
THEN
  SUM(
    PreviousPeriods(
      [Time].CurrentHierarchy.Levels("Day").CurrentDateMember),
    ([Measures].[Issues with DE - Completion Due Date (F2C)],[Country Review DE].[CALCULATED_MEMBER_NAME])
  )
WHEN
  DateAfterPeriodEnd("Today", [Time].CurrentHierarchyMember)
  OR
  DateInPeriod("Today", [Time].CurrentHierarchyMember)
THEN
  SUM(
    PreviousPeriods(
     [Time].CurrentHierarchyMember),
    ([Measures].[Issues with DE - Completion Due Date (F2C)],[Country Review DE].[CALCULATED_MEMBER_NAME])
  )
END

Replace CALCULATED_MEMBER_NAME with the name you gave to the Country Review DE dimension calculated member created in the first step.

Best,
Roberts // support@eazybi.com