none
SSAS - MDX - Last child from distinct count RRS feed

  • Question

  • I have a measure which is created as Distinct Count on column (Surrogate_Part_ID). It is OK, but I need in row "Sum" a value = 255 147, because now is returned distinct count for two days (21.10. and 22.10.).

    So is it possible create calculated aggregation something as LastChild from distinct count? [Measure].[Distinct Count Parts].[LastChild] - is it possible?


    Thursday, November 15, 2018 8:51 AM

All replies

  • Looking at your multiselect, you'll first need a dynamic set of [Calendar].[Date].[Date], let's say [DateSet].

    Then your new calculated measure could be

    ( Tail ( NonEmpty ( EXISTING [DateSet]
                        , [Measures].[Distinct Count Parts]
    ) ).Item(0).Item(0) , [Measures].[Distinct Count Parts] )


    Expect me to help you solve your problems, not to solve your problems for you.

    Thursday, November 15, 2018 2:54 PM
  • You could also just put this in a scope assignment to target the all member of the date hierarchy and overwrite that rather than creating a separate calculated measure.

    eg.

    CREATE DYNAMIC HIDDEN SET CURRENTCUBE.[DateSet] As [Date].[Date].[Date].Members;

    SCOPE([Measure].[Distinct Count Parts], ROOT([Date]));
    this = 

    ( Tail ( NonEmpty ( [DateSet]
                        , [Measures].[Distinct Count Parts]
                       )
            ).Item(0).Item(0)
     , [Measures].[Distinct Count Parts]
    );


    END SCOPE;


    http://darren.gosbell.com - please mark correct answers

    Thursday, November 15, 2018 9:50 PM
    Moderator