CODEWITHSUNDEEP
ssasmdx
RB84
RB84

Reputation: 341

Limit a KPI Value to multiple members of the same hierarchy

A company I am working for requests a KPI concerning overtime hours booked. They want to measure if it's below their set threshold of 3%. There is a calculated member, named [Measures].[Percentage of Overtime Hours (%)], which can be used.

However, they want to limit the KPI to just the 2 departments they are available for.

I tried to limit the value by specifying this KPI Value expression:

([Measures].[Percentage of Overtime Hours (%)], {[Project].[Project Department].&[Department A], [Project].[Project Department].&[Department B]})

However, that gives me the following error message:

#Error The  function expects a string or numeric expression for the  argument. A tuple set expression was used.

On MSDN I could not find an answer to this. Google didn't yield me any decent results because I don't know what exactly to search for. Limiting a measure just gives me tons of articles about how the FILTER expression works, which seems not to be what I'm looking for.

How can I limit the KPI value to just the 2 departments?

I tried using SCOPE statement as suggested.

CREATE MEMBER  CURRENTCUBE.[Measures].[Percentage of Overtime Hours (%) for AB] AS null;

SCOPE([Measures].[Percentage of Overtime Hours (%) for AB]);
    This = NULL;

    SCOPE([Project].[Project Department].&[Company A]);
        This = ([Measures].[Percentage Overtime (%)], [Project].[Project Department].&[Company A]);
    END SCOPE;

    SCOPE([Project].[Project Department].&[Company B]);
        This = ([Measures].[Percentage Overtime (%)], [Project].[Project Department].&[Company B]);
    END SCOPE;

    SCOPE([Project].[Project Department].[All]);
        This = AGGREGATE({[Project].[Project Department].&[Company A], [Project].[Project Department].&[Company B]}, [Measures].[Percentage Overtime (%)]);
    END SCOPE;
END SCOPE;

This however seems to push the same issue down one level deeper. It becomes clear when viewing [Measures].[Percentage of Overtime Hours (%) for AB] at the [Project].[Project Department].[All] level. It returns NULL. Any other suggestions?

Upvotes: 0

Views: 160

Answers (1)

whytheq
whytheq

Reputation: 35605

You're getting the error because you're using a tuple (...) and inside the tuple you have a single member, followed by a set:

( //<<braces indicate a tuple
  [Measures].[Percentage of Overtime Hours (%)]    //<<single member
 ,{
    [Project].[Project Department].&[Department A]  //<<two member set
   ,[Project].[Project Department].&[Department B]
  }
)

If your adding this to your cube script then can you do a scoped assignment?

I have very limited experience with these assignments but maybe something like the following:

CREATE MEMBER  currentcube.[Measures].[Percentage of Overtime Hours (%) for AB]
AS null;
SCOPE( ({
    [Project].[Project Department].&[Department A]
   ,[Project].[Project Department].&[Department B]
  },
  [Measures].[Percentage of Overtime Hours (%)]
 ));
this = [Measures].[Percentage of Overtime Hours (%)];
END SCOPE;

Upvotes: 0

Related Questions