I have the following list of all measure codes. The ACM Item Number and Comment are meaningless and therefore must be excluded from the list. These measure codes have ‘NONE’ uom_code.
So my goal is to get a list of measure codes where the uom_code is not NONE but the DELAY and MEETING measure codes are included in MDX. The DELAY and MEETING are also the measure codes with NONE uom_code.
In T-SQL, this is easy:
SELECT
DISTINCT measure_code
FROM
dm_measure
WHERE
uom_code <> ‘NONE’
OR
measure_code IN (‘DELAY’, ‘MEETING’)
How to do this in MDX? First of all, check the dimension hierarchies to see how they are all related (see below as an example).
The Measure dimension has 2 hierarchies, i.e. Compounds and Measures. The Measures hirearchy has 2 levels, i.e. Measure and Uom. In order to get to level Uom from measure, use DESCENDANTS() function and set the level to 1. To exclude ‘None’ uom_code, use FILTER() function.
Syntaxes:
DESCENDANTS(<Member>, <Level>)
FILTER(<Set>, <condition>)
To get a list of measure codes (uniquename and name) where the uom is not ‘NONE’ in MDX:
WITH
MEMBER [MeasureUniqueName] AS [Measure].[Measures].CURRENTMEMBER.UNIQUENAME
MEMBER
[MeasureEnglishCaption] AS [Measure].[Measures].CURRENTMEMBER.NAME
SET
CalcMeasures AS FILTER(DESCENDANTS([Measure].[Measures].[Measure], 1), [Measure].[Measures].CURRENTMEMBER.NAME <> "none")
SELECT
{[MeasureUniqueName],[MeasureEnglishCaption] } ON COLUMNS
,
ORDER(CalcMeasures, [MeasureEnglishCaption], BASC) ON ROWS
FROM
InSiteCube
The output of the above query:
As you can see, the MeasureEnglishCaption field contains uom_code. I want it to contain measure_code. To do this, use subselect. Bear in mind that subselect in MDX works on COLUMN axis only.
WITH
MEMBER [MeasureUniqueName] AS [Measure].[Measures].CURRENTMEMBER.UNIQUENAME
MEMBER
[MeasureEnglishCaption] AS [Measure].[Measures].CURRENTMEMBER.NAME
SET CalcMeasures AS [Measure].[Measures].[Measure].ALLMEMBERS
SELECT
{ [MeasureUniqueName],[MeasureEnglishCaption] } ON COLUMNS
,
ORDER(CalcMeasures, [MeasureEnglishCaption], BASC) ON ROWS
FROM (
SELECT {FILTER(DESCENDANTS([Measure].[Measures].[Measure], 1), [Measure].[Measures].CURRENTMEMBER.NAME <> "none"), [Measure].[Measures].[Measure].&[DELAY],[Measure].[Measures].[Measure].&[MEETING]} ON COLUMNS
FROM InSiteCube
)
Output:
The above query now gives me a list of measure codes where the uom_code is not ‘NONE’ but delay and meeting measure codes are included. 🙂
