Check if level in any dimension hierarchy is [Foo]

In my Date Dimension I have 2 hierarchies:

[Year - Week - Date] and [Year - Month - Date]

I want to check in a Cube Calculation if the current level of the Date Dimension is [Date] (the lowest level) or something higher.

How can I achieve this?

Background: I need this to calculate how many working days there were in a period, for an employee. I currently have this code (untested) that should do the trick for 1 hierarchy, but I guess this will fail when users are using the [Year - Week - Date] hierarchy.

CASE WHEN
    [Date].[Year - Month - Date].CURRENTMEMBER.Level
IS
    [Date].[Year - Month - Date].[Date]
THEN
    //at day level,

    //if there is any duration booked, this is a working day
    IIF([Measures].[Duration] = 0,0,1)

ELSE
    //at higher than day level,

    //count days
    COUNT(
        // where duration > 0 (employee work day)
        FILTER(
            Descendants([Date].[Year - Month - Date].CURRENTMEMBER, [Date].[Year - Month - Date].[Date]),
            [Measures].[Duration] > 0
        )
    )

END

开发者_Go百科tl;dr how do I make the above code also work for [Year - Week - Date] hierarchy in the cleanest way possible.

Let's assume the hierarchy (aka attribute) [Date].[Date] exists. If this is the case you can simplify :

  COUNT(
    FILTER( Existing [Date].[Date].members, [Measures].[Duration] > 0 )
  )

The Existing will force to apply an autoexists on the [Date] dimension. This is mainly a performance improvement as it's avoid to evaluate (fact-vise) all tuples.

Once the former examples is clear, we can merge it with your version for the fastest solution (no need for the first iif) :

  COUNT(
    FILTER( Existing 
       Descendants([Date].[Year - Month - Date].CURRENTMEMBER, [Date].[Year - Month - Date].[Date],self) 
       , [Measures].[Duration] > 0 )
  )

Further improvements may be possible adding a new measure with a different aggregation type or adding an iif to change which of the two hierarchies is used for descendants (e.g. the one where the currentmember and the defaulmember is not equal)

If you are looking for the cleanest way to calculate how many working days there were in a period, you have to emulate the behavior of regular measure. Otherwise you will get error or bad data in the case of a multiselect on [Date]. In addition, it is desirable to get rid of the Count(Filter(...)) expression to keep the block computation mode (see Optimizing Count(Filter(...)) expressions in MDX). To do this, follow these steps:

1. Go to the data source view.
2. Create a new named calculation next to the [Duration] column (in the same fact table).
3. Column name is "Working days", expression is "null".
4. Create a new regular measure based on "Working days" column. Aggregation function is Sum.

5. Write in the MDX script:

   (
       [Date].[Date].[Date].Members,
       [Measures].[Working days]
   ) = Iif( [Measures].[Duration] > 0, 1, null );
   

IsLeaf() will tell you if a member is at the bottom level.

Look at SCOPE commands in your calculation script, I do something similar for my YMD calender and my YWD calendar:

CREATE MEMBER CurrentCube.Measures.Example AS NULL //Dummy will be calc'd later

SCOPE (DESCENDANTS([Date].[Calender Y M D],,AFTER));    
 Measures.Example = 1; //Fill in for monthly calcs
END SCOPE

SCOPE (DESCENDANTS([Date].[Calender Y W D],,AFTER));    
 Measures.Example = 2; //Fill in for weekly calcs
END SCOPE

The syntax with the ,,AFTER is to exclude the All member if I remember rightly, I'm trying to dif the link up but can't find it. Alternatively, if the calculation works well with the ALL member, just use SCOPE([Date].[Calender Y W D])