April 16, 2015 at 5:36 am
Hi, I have a filtered measure which is not working correctly and I hope someone can help me with it.
I have a measure which is a simple count of rows:
[Measures].[MyTableCount]
The calculated measure I need help with uses this count, but filters it using a dimension attribute:
CREATE MEMBER CURRENTCUBE.[MEASURES].[MyFilteredCount]
AS ([Measures].[MyTableCount],[MyDimension].[AttributeFlag].&[Y] ),
FORMAT_STRING = "#,#",
VISIBLE = 1;
When I run an example with lots of filters in the Browser pane to return a single problem row(i.e. AttributeFlag=N) I get:
filtered measure value=1,
simple count measure=(null).
If I modify the filters (in the Browser pane) to include all values for the [AttributeFlag] i.e. to include "Y" also, I get:
filtered measure value=1,
simple count measure=1
My instinct is that I have a join problem between dimensions but I wanted to check if I have an obvious MDX error (I am an MDX novice).
(Edit: Perhaps I need to create a new measure group for the calculated measure and join it to the dimensions?)
(Edit2: I found a solution - I added an IIF(
[Measures].[MyTableCount] > 0, ... ,NULL) around the calculation.
I'd be interested if there is a more efficient method, though)
Thanks in advance.
May 1, 2015 at 7:40 am
I think it is probably a join or dimensional usage issue in your cube. I am not able to reproduce your scenario, and the syntax is definitely ok.
May 1, 2015 at 8:41 am
I eventually found an easier and less complicated solution. I created a new column with int values (Y=1, N=0) and then did a straight forward measure on it. Works every time, no need for calculated measure / extra measure group /additional joins and performance is excellent.
May 1, 2015 at 9:42 am
That is also my preferred solution most of the time. If I can handle it in the table, better! Straight forward measures and no complications.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply