MDX divide a set by a measure

Question

I have a set lets just say:

set [A] as {
([Measures].[X],[somedimension].[A])
[Measures].[Y],[somedimension].[A])
[Measures].[Z],[somedimension].[A])
}

What I need to do is I have to divide this set with a specific value say: [Measures].[P] Is it possible to do something like this in MDX? If yes then how. Because if I use a normal divide operation it fives an error saying "The Divide function expects a string or numeric expression for the 1 argument. A tuple set expression was used"

VasiliyKapitanskiy VasiliyKapitanskiy · Accepted Answer · 2018-10-17T09:22:41

SET usually is just a list of items from a dimension. Use FILTER with needed condition to get items which will satisfy it.

WITH
SET [A] AS {Your Set Members}
SET [A WITH P Over 100] AS FILTER([A], [Measures].[P] > 100)
SET [All Others] AS [A] - [A WITH P Over 100] -- Just for example
SELECT { [P] } ON COLUMNS,
{[A WITH P Over 100]} ON ROWS
FROM [Your Cube]
WHERE ([P] < 1000)

MDX divide a set by a measure

1 Answers