How to use IsLeaf in mondrian mdx formula?

Question

Trying to recreate this formula in Pentaho Mondrian Cube.

iif(ISLEAF([Time].[Month].CurrentMember)),[Measures].m1,0)

This formula is being used in SSAS cube already. Need to recreate similar formula in Pentaho Mondrian Cube.

Can IsLeaf be used in mondrian or is there any alternative for this?

Answer 1

Something like the following should work at any arbitrary level within the default hierarchy.

It will return [Mesures].m1 for the last year, which (has non-empty m1 measure and satisfies your filter conditions) in case you select [Time].[Year] members.

Or it will return the measure for the last month of the last year, which has non-empty m1 measure in case you select [Time].[Month] members.

Though I don't think it will work if you mix members for different levels (e.g. WITH SET time_members AS {[Time].[2017], [Time].[2017].[1]. [Time].[2017].[1].[31]})

If you don't need such a generic approach, then this solution may be simplified and the measure calculation speed may be improved.

Warning: the query may cause 100% CPU utilization (or may not, I am not sure), thus making your server unresponsive. So, choose your testing environment carefully.

Disclaimer: I don't have mondrian to test at the moment, so the following example is very likely to have errors.

Iif(
    // Check if current [Time] member is the last member:
    [Time].CurrentMember
    IS
    // Take the 0th member from the end of the set (AFAIK, mondrian sets are guaranteed to be ordered):
    Tail(
        // AFAIK, Level.Members returns the full set of members, disregarding query filters.
        // So I use Filter function to filter members, which don't exist in context of current cell.
        // It should leave only members, which (are related to current cell and satisfy filter conditions of the query).
        Filter(
            [Time].CurrentMember.Level.members
            // If your measure is nullable, then you might want to use count measure in this condition instead of m1:
            , NOT IsEmpty([Measures].m1)
        )
        // Number of members to get by the Tail() function:
        , 1
        // Return the only member of the set as a Member (not as a Set):
    ).Item(0)
    // return if true
    , [Measures].m1
    // else:
    , 0
)

Some points that may be problematic and need to be tested:

1. How the measure is calculated if the last [Time] member has empty m1 measure (if this is a valid case for your measure)

2. How the measure is calculated at different levels of [Time] hierarchy.

3. How the measure is calculated if you don't use [Time] dimension in report explicitly.

4. How the measure is calculated if you use [Time] dimension on slicer axis only (in WHERE condition)

5. How the measure is calculated if you use restricted set of [Time] members, e.g. explicitly enumerating members in set literal (e.g. {[Time].[2006].[01], [Time].[2006].[02]}) or via using Filter() function on the dimension.

6. How the measure is calculated if you use fiters on other dimensions/measures.

7. How the measure is calculated at calculated members of [Time] dimension (including totals and sub-totals generated by Analyzer).

8. How the measure is calculated if you select members from different levels on the same axis.