# question

## Min of LastNonEmpty values

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 ?

image.png (42.5 KiB)
image.png (48.9 KiB)
image.png (10.8 KiB)

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

image.png (3.3 KiB)

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Vote"

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.

· 5

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Maybe my question wasn't clear enough.

So I add an example of data sources to the description.

I have already tried to do a Min on my lastNonEmpty values, but it gives me the absolute min value, that is 1 on my data samples.
Nevertheless I think this is the right way.

JuanJimenez-8068

With a correctly working LastNonEmpty base measure, you shouldn't see 1 at all unless you filter on dates earlier than Jan 5th. First make sure it works, that you have your date dimension properly marked as such.

1 Vote 1 ·
AlexeiStoyanovsky

The lastNonEmpty works fine.
At least I think so.

I filter the whole 2021. All dates in my sample data.

AlexeiStoyanovsky

Ok you are right, the Min works perfectely I think.
The noise come from à scope that create a kind of "LastEverNonEmpty"

something like

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

What am I doing wrong ?

1 Vote"

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

image.png (18.8 KiB)
· 3

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Maybe my question wasn't clear enough.

So I add an example of data sources to the description.

Using the Min aggregation does not work.
The measure has to be lastNonEmpty aggregation.
Changing to Min, it just give me the absolute Min value.