question

JuanJimenez-8068 avatar image
0 Votes"
JuanJimenez-8068 asked JuanJimenez-8068 answered

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:
83047-image.png

Here is two examples:
82717-image.png
82704-image.png

Can anyone help ?


sql-server-analysis-services
image.png (42.5 KiB)
image.png (48.9 KiB)
image.png (10.8 KiB)
5 |1600 characters needed characters left characters exceeded

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

JuanJimenez-8068 avatar image
0 Votes"
JuanJimenez-8068 answered

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.
84735-image.png
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.





image.png (3.3 KiB)
5 |1600 characters needed characters left characters exceeded

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

AlexeiStoyanovsky avatar image
1 Vote"
AlexeiStoyanovsky answered AlexeiStoyanovsky commented

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
5 |1600 characters needed characters left characters exceeded

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

Thanks for your answer.

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.

0 Votes 0 ·

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 ·

The lastNonEmpty works fine.
At least I think so.

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

0 Votes 0 ·

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])
                     )
                     ,[Measures].[Last Password set Native]
             );
 END SCOPE; 


What am I doing wrong ?

0 Votes 0 ·
Show more comments
LukasYu-msft avatar image
1 Vote"
LukasYu-msft answered JuanJimenez-8068 commented

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.
83091-image.png
This should change aggregation behavior of this measure.

Regards,
Lukas



image.png (18.8 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

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

Thanks for your answer.

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.

0 Votes 0 ·

Have you tried to make calculated measure as above post mentioned ? Did you make any success with that approach?

0 Votes 0 ·

Directely this approach does not work for me.
But thanks to both of you I found a way.
I Will post a comment with the problem and the solution.

0 Votes 0 ·