none
KPI and MDX formula RRS feed

  • Question

  • Hi, I need to put in a KPI the sum of values for the next conditional as actual value:

    Sum all inspection score from  the all inspection that  "the date completed <= dead Line date"  and the "inspection date between 2009-01-01 and 2009-03-30"

    So I created my cube and I have a score like Measure, and I have date completed, dead Line date and inspection date as members of my dimension inspection, well I tried to run this:


    Sum( { [Data Table 1 - Request Date].[Quarter Names].&[Quarter 1  2009], [Data Table].[Is Valid Complete].&[True] },  [Measures].[score] )

    One of problem is the formula is wrong because the members don't belong to the same hierachies, so I tried with this:


    Sum( GENERATE( [Data Table 1 - Request Date].[Quarter Names].&[Quarter 1 2009], LINKMEMBER( [Data Table].[Is Valid Complete].&[True], [Data Table].[Is Valid Complete] ) ), [Measures].[score] )

    The problem with this formula is that this give me more rows, not only the rows that I need ( in fact the real value should be 44 but this formula returned me 10324, this formula give me other rows that aren't in the condition ),  Anybody have some expirence with this type of calculations?

    Thanks to all

    Monday, May 11, 2009 11:02 PM

Answers

  • This is just a tuple and does not require the use of the SUM aggregate function.

    ([Data Table 1 - Request Date].[Quarter Names].&[Quarter 1  2009], [Data Table].[Is Valid Complete].&[True],  [Measures].[score] )


    That will return the answer that you are looking for.

    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    • Marked as answer by JCJL Tuesday, May 12, 2009 3:58 PM
    Tuesday, May 12, 2009 1:59 AM

All replies

  • This is just a tuple and does not require the use of the SUM aggregate function.

    ([Data Table 1 - Request Date].[Quarter Names].&[Quarter 1  2009], [Data Table].[Is Valid Complete].&[True],  [Measures].[score] )


    That will return the answer that you are looking for.

    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    • Marked as answer by JCJL Tuesday, May 12, 2009 3:58 PM
    Tuesday, May 12, 2009 1:59 AM
  • Dan, you're right, Thank you very much

    Tuesday, May 12, 2009 3:59 PM