Mdx - Flag -Actual

Question

I have two dimensions DimFlag and DimPNL and a fact table FactAmount . I am looking to : When pnl is stat(Is Stat=1) : sum (Actual x FlagId ) For pnl I multiply the amounts by field FlagId basically if it will be so 0 0 X = 0 ..

DimFlag

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

DimPNL

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

FactAmount

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

I tried the following MDX but it didn't work, any idea please ?

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

Cannot figure out what you are trying to aggregate (assuming Aggregate function just executes default aggregation, which can be count or whatever). Try this:

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*