Mdx - Flag -Actual

Saturday, July 5, 2014

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)
);






http://ift.tt/1xv3PNp