How to check dimension value in CASE function and then perform measure calculation?

CASE [Label].CurrentMember.Level.Name
WHEN ‘PI-6’ THEN ‘N/A’
WHEN ‘PI7’ THEN
([Measures].[Sprint issues committed] - PreviousRowValue([Measures].[Sprint issues committed]))/ PreviousRowValue([Measures].[Sprint issues committed])
END

Hello @ysharma

I think this is a “formatting” issue. When PI-6 your formula returns a string (‘N/A’) and when PI7 it returns a number (that you format as percentage). Value “infinity” may be the result of dividing N by A…

What is happening if you change the ‘N/A’ to a numeric value?
Or try the other way around… Change the formatting of your “improvement” to “default”. If the result (number 2) is displayed correctly we will add the “%” later! :slight_smile:

Hope it helps!
VasileS.

1 Like

Thank you so much @VasileS
I changed it to default and not sure what I changed but now even that ‘2’ is not coming now…

CASE [Label].CurrentMember.Level.Name
WHEN ‘PI-6’ THEN 0
WHEN ‘PI7’ THEN
([Measures].[Sprint issues committed] -
PreviousRowValue([Measures].[Sprint issues committed]))/
PreviousRowValue([Measures].[Sprint issues committed])
END

it’s because “[Label].CurrentMember.Level.Name”. Use CurrentHierarchyMember.Name

Try to go step by step and see what is shown.

I used “Issue type” instead of “Label” for my calculated measure - you’ll figure it. Format is ‘decimal’:

--[Issue Type].CurrentMember.Level.Name
--[Issue Type].CurrentHierarchyMember.Name
CASE [Issue Type].CurrentHierarchyMember.Name
  WHEN "Epic" THEN 9999
  WHEN "Task" THEN 
    IIf(  -- to avoid 'division by zero'!
       [Measures].[Issues resolved] > 0,
       [Measures].[Open issues] / [Measures].[Issues resolved],
       9999
    )
END

/* ***
CASE [Label].CurrentMember.Level.Name
  WHEN ‘PI-6’ THEN 0
  WHEN ‘PI7’ THEN
    ([Measures].[Sprint issues committed] -
    PreviousRowValue([Measures].[Sprint issues committed]))/
    PreviousRowValue([Measures].[Sprint issues committed])
END
*** */

Then create a new measure to display the percentage (format: default):

IIf(
  [Measures].[zzz] > 100,
  'N/A',
  Format([Measures].[zzz],'###.##%')
)

My report looks like this:
image

You may want to use FORMAT in your formula, so there’s no need to have multiple custom measures. I keep them so the calculation steps can be clearly visible.

References
EazyBI docs: Format
MDX reference: FORMAT_STRING Contents (MDX) | Microsoft Learn

Kind regards,
VasileS.

Now actually I have tried this below
CASE
WHEN [Label].CurrentMember.Name MATCHES ‘PI-6’
THEN
‘N/A’
WHEN [Label].CurrentMember.Name MATCHES ‘PI7’
THEN
([Measures].[Sprint issues committed])
END
and I am getting this

If I Do like this
CASE
WHEN [Label].CurrentMember.Name MATCHES ‘PI-6’
THEN
‘N/A’
WHEN [Label].CurrentMember.Name MATCHES ‘PI7’
THEN
‘OK’
END

Then this is the result

You are on the right track!

Change the ‘OK’ in your code here:

WHEN [Label].CurrentMember.Name MATCHES ‘PI7’
   THEN
   ‘OK’     -- <--- replace this with the code below
END

as follows:

  Format(
        ([Measures].[Sprint issues committed] - PreviousRowValue([Measures].[Sprint issues committed])) 
           /
         PreviousRowValue([Measures].[Sprint issues committed])     
       ,
        '###.##%')

Make sure PreviousRowValue([Measures].[Sprint issues committed] is not zero!

Kind regards,
VasileS.

1 Like

I have done something like below
One customer measure : Improvement in Committed SP
[
CASE [Label].CurrentMember.Name
WHEN ‘PI5L’ THEN
([Measures].[Sprint Story Points committed] -PreviousRowValue([Measures].[Sprint Story Points committed]))
/PreviousRowValue([Measures].[Sprint Story Points committed])
WHEN ‘PI6L’ THEN
([Measures].[Sprint Story Points committed] -PreviousRowValue([Measures].[Sprint Story Points committed]))
/PreviousRowValue([Measures].[Sprint Story Points committed])
WHEN ‘PI7L’ THEN
([Measures].[Sprint Story Points committed] -PreviousRowValue([Measures].[Sprint Story Points committed]))
/PreviousRowValue([Measures].[Sprint Story Points committed])
END]

Formatting is default…

then second custom measure is: Improvement in Committed SP Percent
[
IIf(
[Measures].[Improvement in Committed SP] > 100,
‘N/A’,
Format([Measures].[Improvement in Committed SP] ,‘###.##%’)
)]
Then it shows like below table… which then I remove first custom measure “Improvement in Committed SP” and keep only second measure Improvement in Committed SP Percent

image

1 Like