Post reply

filtered measure problem

  • April 16, 2015 at 5:36 am

    #150336

    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

    #1794981

    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

    #1795009

    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

    #1795028

    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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy