通过过滤度量值在 MDX 中定义计算成员 [英] Define a calculated member in MDX by filtering a measure's value

问题描述

我需要在 MDX 中定义一个计算成员(这是 SAS OLAP，但我很感谢那些使用不同 OLAP 实现的人的回答).

I need to define a calculated member in MDX (this is SAS OLAP, but I'd appreciate answers from people who work with different OLAP implementations anyway).

新度量的值应通过应用附加过滤条件从现有度量计算.我想举个例子会更清楚:

The new measure's value should be calculated from an existing measure by applying an additional filter condition. I suppose it will be clearer with an example:

• 现有衡量标准:总流量"
• 现有维度:方向"(进"或出")
• 我需要创建一个计算成员传入流量"，它等于带有附加过滤器的总流量"(Direction = In")

问题是我不了解 MDX，而且我的日程安排非常紧张(对于新手问题，我深表歉意).我能想到的最好的方法是:

The problem is that I don't know MDX and I'm on a very tight schedule (so sorry for a newbie question). The best I could come up with is:

([Measures].[Total traffic], [Direction].[(All)].[In])

这几乎可以工作，除了具有特定方向的单元格:

Which almost works, except for cells with specific direction:

所以看起来方向上的内在"过滤器被我自己的过滤器覆盖).我需要内在"过滤器和我自己的过滤器的交集.我的直觉是，它与 Intersecting [Direction].[(All)].[In] 与被评估单元格的内在坐标有关，但如果没有，我很难知道我需要什么第一次阅读这个主题:)

So it looks like the "intrinsic" filter on Direction is overridden with my own filter). I need an intersection of the "intrinsic" filter and my own. My gut feeling was that it has to do with Intersecting [Direction].[(All)].[In] with the intrinsic coords of the cell being evaluated, but it's hard to know what I need without first reading up on the subject :)

[更新]我最终得到了

IIF([Direction].currentMember = [Direction].[(All)].[Out],
    0,
    ([Measures].[Total traffic], [Direction].[(All)].[In])
)

..但至少在 SAS OLAP 中，这会导致对基础数据集执行额外的查询(以计算 [in] 的值)，所以我最终没有使用它.

..but at least in SAS OLAP this causes extra queries to be performed (to calculate the value for [in]) to the underlying data set, so I didn't use it in the end.

推荐答案

首先，您可以在 MDX 中定义一个新的计算度量，并告诉它使用另一个度量的值，但应用了过滤器:

To begin with, you can define a new calculated measure in your MDX, and tell it to use the value of another measure, but with a filter applied:

WITH MEMBER [Measures].[Incoming Traffic] AS
'([Measures].[Total traffic], [Direction].[(All)].[In])'

每当您在报表上显示新度量时，无论是否使用方向"维度，它都会表现得好像它有一个方向 > In"过滤器.

Whenever you show the new measure on a report, it will behave as if it has a filter of 'Direction > In' on it, regardless of whether the Direction dimension is used at all.

但是在您的情况下，您希望在使用时优先使用方向维度......所以事情变得有点混乱.您必须检测此维度是否正在使用中，并采取相应措施:

But in your case, you WANT the Direction dimension to take precendence when used....so things get a little messy. You will have to detect if this dimension is in use, and act accordingly:

WITH MEMBER [Measures].[Incoming Traffic] AS
'IIF([Direction].currentMember = [Direction].[(All)].[Out],
    ([Measures].[Total traffic]),
    ([Measures].[Total traffic], [Directon].[(All)].[In])
)'

要查看 Dimension 是否正在使用，我们检查当前单元格是否正在使用 OUT.如果是这样，我们可以按原样返回 Total Traffic.如果没有，我们可以告诉它在我们的元组中使用 IN.

To see if the Dimension is in use, we check if the current cell is using OUT. If so we can return Total Traffic as it is. If not, we can tell it to use IN in our tuple.

这篇关于通过过滤度量值在 MDX 中定义计算成员的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！