#VALUE! shows in calc'd measure but there is no associated error

Question

Hi.  I just added a calc'd measure as ([A]) + ([B]) all associated with the same measure group.  A & B come from the star and have display characteristic of currency but storage type of double.  When I hover over the new measure (display currency) I see the correct sum in the pop up instead of an error message but #VALUE! in the pivot cell.  I cannot see an error.  The value is around 42 million dollars.  A and B show fine.  What am I missing?  There are no nulls in the star's A and B columns.  The cube processes fine.

Answer 1

Hi db,

If you create the measure like this it should be fine:

CREATE MEMBER CURRENTCUBE.[Measures].[new measure] AS
  [Measures].[A]+[Measures].[B],
FORMAT_STRING="Currency";

If you can't get it to work can you please post the calc member script here? 

---

Boyan Penev --- http://www.bp-msbi.com

Answer 2

Thx Boyan.  Yes it was already coded like this

CREATE

MEMBER CURRENTCUBE.[Measures].[A + B]

 

AS ([Measures].[A]) + ([Measures].[B]),

FORMAT_STRING

= "Currency",

VISIBLE

= 1 , ASSOCIATED_MEASURE_GROUP = 'Fact X' ;

before I posted.   The sql data types of A and B in the star are "money".  Their ssas display attribute is currency.

I probably didnt need the parens but I have them coded that way on another calc'd member in a different mg that works fine.  I'm going to  poke around a little more too.  This is baffling me a little. 

Answer 3

I think I found it but dont have absolute proof.  It is working though.

Column B was recently introduced as part of this effort too.

On the DSV design diagram where I "refreshed" and ssas detected earlier that I was adding column B (one of the measures used in the new calc), I right clicked the new column B and chose it's properties.

The Friendly Name was blank and that looked like the only dsv property attribute that differed from another participant's dsv properties used in a very similar calculation.  I entered it and reprocessed and things look fine.  I have no idea if that made the difference and why ssas didnt display the error when I first pivoted.

Interestingly, before the fix, when I dragged column B to the pivot area for initial testing, it and Column A displayed fine.  I'm baffled and may look up what / if MS considers the dsv's Friendly Name important and why it didnt simply default on refresh.  The cube design panel had no problem allowing me to add column B to the measure group's list of measures when the problem was unfolding and before I added the calc'd measure.