Mdx - 标志 - 实际

Question

我有两个维度 DimFlag 和 DimPNL 以及一个事实表 FactAmount 。我正在寻找:当 pnl 是 stat(Is Stat=1) 时:sum (Actual x FlagId)对于 pnl，我基本上将金额乘以字段 FlagId，如果它是 0 0 X = 0 ..

昏暗的旗帜

FlagId  FlagLabel
-----------------
1       NotClosed
0       IsClosed

暗淡的PNL

PNLId  PNLName  Is Stat
1       a        1
2       test     1
3       test2    0

事实数量

  id    PNLId     FlagId  Actual
  1      1        1        100
  2      2        1        10
  3      3        0        120

我尝试了以下 MDX，但没有用，请问有什么想法吗？

Scope (
        [Dim PNL].[PNL].members,[Measures].members     

);   


this = iif([Dim PNL].[PNL].CurrentMember.Properties("Is Stat") =1 
,
aggregate([Dim PNL].[PNL].currentmember,[Measures].currentmember)* iif([Dim Flag].[Flag Label].[Flag Label].currentmember = 0, 0, 1),
aggregate([Dim PNL].[PNL].currentmember,[Measures].currentmember)
);

Answer 1

无法确定您要聚合的内容(假设 Aggregate 函数只执行默认聚合，可以是计数或其他)。试试这个:

with

-- check if PNL flag has IS_Stat = 1 value
-- actually it is better to add IS_Stat as attribute, but nvm
member isPNL as
(
  iif([Dim PNL].[PNL].CurrentMember.Properties("Is Stat")=1,1,0)
)

-- calculate FlagID * Actual. May require leaf level calculation, but 
-- I am not sure without checking your cube structure
member flagActualPair as
(
  [Measures].[Actual] * CINT([Dim Flag].[Flag Label].[Flag Label].currentmember.memberValue)
)

-- get sum of Actual * FlagID
member totalPNL as
(
  sum(EXISTING [Dim PNL].[PNL].members, [Measures].[flagActualPair])
)

-- final query - filter PNL axis, filtering out PNL's with IS_Stat = 0
select
{
  [Measures].[totalPNL]
} on 0,
{
  FILTER([Dim PNL].[PNL].allmembers, [Measures].[isPNL] = 1)
} on 1
from *YourCube*