Compare 2 Fields and based on condition show Result

Questions & Answers
1

There are 2 Fields called Issue Completion Criteria and Issue Status.

The Condition is

  • If Issue Status is Closed and Issue Completion Criteria is Live Then it should show Completion Criteria Not Achieved
  • If Issue Status is any status except Closed or Live and Issue Completion Criteria is Dev Complete or QA Complete then it should show Completion Criteria Not Achieved
  • If Issue Status is “Live” and Issue Completion Criteria is Dev Complete,QA Complete& Live. Then it should show Completion Criteria Achieved
  • If Issue Status is Closed and Issue Completion Criteria is DEV Complete, QA Completed then it should show Completion Criteria Achieved

Is it possible ? If so can you let me know the mdx for it ?

2

Hi,

Assuming that the report uses Issue dimension and you have imported the Completion Criteria custom field, the custom formula could look like this:

CASE 
  WHEN [Measures].[Issue status]="Closed" AND 
    [Measures].[Issue Completion Criteria]="Live"
  THEN "Completion Criteria Not Achieved"
  WHEN [Measures].[Issue status]<>"Closed" AND [Measures].[Issue status]<>"Live"
    AND [Measures].[Issue Completion Criteria] ="Dev Complete,QA Complete& Live"
   THEN "Completion Criteria Not Achieved"
  WHEN [Measures].[Issue status]="Live"
     AND [Measures].[Issue Completion Criteria]="Dev Complete,QA Complete& Live"
   THEN "Completion Criteria Achieved"
  WHEN [Measures].[Issue status]="Closed" AND 
    [Measures].[Issue Completion Criteria]="DEV Complete, QA Completed"
    THEN "Completion Criteria Achieved"
END

Please check the spelling of the statuses and completion criteria.

Kindly,
Janis, eazyBI support

3

Thanks @janis.plume for this clarification.