# Min of LastNonEmpty values

101 Reputation points
2021-03-30T10:12:21.977+00:00

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 ?

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,265 questions

1. 101 Reputation points
2021-04-06T06:50:01.66+00:00

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])
)
);
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])
)
,
);
END SCOPE;
SCOPE ([Compte].[Account name].[Account name]);
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.

PS: Please appologise for my english.

1. 3,416 Reputation points
2021-03-30T14:48:54.447+00:00

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.

2. 5,821 Reputation points
2021-03-31T03:04:32.627+00:00

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