Min of LastNonEmpty values

Question

I everybody,

I'am currently fighting with Mdx in a cube about the active directory.

In simple words the cube is :

• an accounts dimension accounts
• a groups dimension
• a dates dimension
• a measure last non empty with the days id of the last password set.

Everything works fine except that I don't whant the last non empty values to be sumed but I want the min a values of the last non empty values .

Here is my data sample:

Here is two examples:

Can anyone help ?

Answer 1

Ok thanks to both of you, I got a sollution.

Here is the problem:
With a simple physical LastNonEmpty measure, I can create a calculated measure that does a MIN on the physical one that does the trick perfectely.
But I want a kind of last ever non empty value that I create with this code :

     SCOPE ([Measures].[Last Password set]);  
         THIS = AGGREGATE(  
                         (  
                             null:ClosingPeriod([Calendrier].[Date].[Date])  
                         )  
                         ,[Measures].[Last Password set Native]  
                 );  
     END SCOPE;   
  

Using directely a calculated measure on that, brings some noise in the EXCEL total cells.

To solve the Issue:
I use 3 measures and two SCOPE.

Last Password set Natif : is a simple physical LastNonEmptyValue
Last Password set LastEverNonEmpty : is a simple physical LastNonEmptyValue too. But calculated as a last ever non empty value within a SCOPE
Last Password set : is a simple physical Min aggregation value. But calculated as MIN(Last Password set LastEverNonEmpty) within an other SCOPE.

Heres is the final codes :

 SCOPE ([Measures].[Last Password set LastEverNonEmpty]);  
    THIS =   
                AGGREGATE(  
                    (  
                        null:ClosingPeriod([Calendrier].[Date].[Date])  
                    )  
                    ,  
                   [Measures].[Last Password set Natif]  
            );  
END SCOPE;  
SCOPE ([Measures].[Last Password set]);  
    SCOPE ([Compte].[Account name].[Account name]);  
        THIS =MIN([Measures].[Last Password set LastEverNonEmpty]);  
    END SCOPE;   
END SCOPE;   

I'am prety sure that this can be optimised and even done with only two measures.
But I don't know how.

Thanks for your time.

PS: Please appologise for my english.

Answer 2

On the outside it's a simple call of the MIN function, with [Measures].[Password last set] as the Numeric_Expression. Your Set_Expression will be as simple as [Accounts].[User].[User] with some luck, but could grow quite complex depending on the precise requirements.

Answer 3

Hi,

If I understand the scene correctly, you could go to cube design- cube structure page, click the measure Password Last Set for its property, then change the AggregareFunction from Sum to Min.

This should change aggregation behavior of this measure.

Regards,
Lukas