Conditional calculation based on hierarchy level using mxd and ssas 2012

0
votes

I am trying to do some conditional logic based on a hierarchy level. In my ssas cube I have the following hierarchy defined:

  • Team
    • Subteam
      • Employee

I want to create a calculated member "efficiency" which does 

DIVIDE([Measures].[A], [Measures].[B])

But only for the Employee level. For all other levels I need to exclude employees where

[Measures].[c] = 1

I'm not sure on how to achieve this and I hope someone can help me.

Thanks ahead!

EDIT My current code works like this. The problem is that the members are not filtered on subteam and team level

    case when [Organigram].[Hierarchy].Currentmember.level IS [Organigram].[Hierarchy].[Employee] 
        then DIVIDE([Measures].[a] , [Measures].[b])
        else 
           case when [Measures].[c] = 0
               then DIVIDE([Measures].[a] , [Measures].[b])
               else NULL 
           end
    END,
1
sql-serverssasmdxhierarchy
You should be able to make a start via IIF and the function 'Level': docs.microsoft.com/en-us/sql/mdx/level-mdx ? Do you want to add this measure to your cube-script or is is just for an MDX script via a WITH clause ? – whytheq
Thank you for your comment. I will supply an edit in my question with my current code. I want to add a measure to my cube (calculations tab in visual studio) – Bart

1 Answers

0
votes

Following on from the comment by @whytheq

Create a new measure using WITH MEMBER and use the IIF test within that. Get something like this working properly first, before you nest a second IIF in there to check [Measures].[c] too...

WITH MEMBER [Measures].[Efficiency] 
AS 'IIF(
   [Organigram].[Hierarchy].Currentmember.level IS [Organigram].[Hierarchy].[Employee], 
   [Measures].[a] / [Measures].[b], 
   NULL
)'
SELECT 
{[Organigram].[NameOfLevel].members} ON ROWS, 
{[Measures].[Efficiency]} ON COLUMNS 
FROM [CubeName]

Once a simple query is working, gradually add little bits to get more complicated.

You can also check what level you're on with the .LevelName and .LevelDepth properties. That might make your MDX shorter and more readable, or maybe not.