MDX flip sign on a measure for specific members on a hierarchy

Question

MDX flip sign on a measure for specific members on a hierarchy

jasonclements32304

Ten Centuries

Points: 1122
More actions
January 20, 2017 at 12:07 pm

#146332

MDX to flip signs on a measure for specific members on a hierarchy Will this work?
What do I have wrong?
Errors:
Error    11    MdxScript(cube_FMS) (21, 4) An expression was expected for the function argument and no expression was detected.        0    0
Error    12    The END SCOPE statement does not match the opening SCOPE statement.        0    0
Error    13    MdxScript(cube_FMS) (25, 1) The END SCOPE statement does not match the opening SCOPE statement.        0    0
Error    14    One or more errors were encountered in the MDX script.        0    0
my calculation tab.
CALCULATE;
SCOPE(
    DESCENDANTS (
        FILTER([Dim FMS Heirarchy].[FMS Reporting Hierarchy].[FMSH FE].allmembers
    ,instr([Dim FMS Heirarchy].[FMS Reporting Hierarchy].currentmember.member_caption,'10-Loan Interest')>0
OR instr([Dim FMS Heirarchy].[FMS Reporting Hierarchy].currentmember.member_caption,'12-Loan Fees')>0
OR instr([Dim FMS Heirarchy].[FMS Reporting Hierarchy].currentmember.member_caption,'13-Investment Income')>0
    OR instr([Dim FMS Heirarchy].[FMS Reporting Hierarchy].currentmember.member_caption,'14-Funding Charge')>0
    OR instr([Dim FMS Heirarchy].[FMS Reporting Hierarchy].currentmember.member_caption,'22-Other Interest Income')>0
OR instr([Dim FMS Heirarchy].[FMS Reporting Hierarchy].currentmember.member_caption,'24-Service Charges And Fees')>0
OR instr([Dim FMS Heirarchy].[FMS Reporting Hierarchy].currentmember.member_caption,'26-Fees and Bounce Safe')>0
OR instr([Dim FMS Heirarchy].[FMS Reporting Hierarchy].currentmember.member_caption,'28-Interchange Income')>0
OR instr([Dim FMS Heirarchy].[FMS Reporting Hierarchy].currentmember.member_caption,'30-Mortgage Loan and Related Fees')>0
OR instr([Dim FMS Heirarchy].[FMS Reporting Hierarchy].currentmember.member_caption,'32-Advisory Service Fees')>0
OR instr([Dim FMS Heirarchy].[FMS Reporting Hierarchy].currentmember.member_caption,'33-SBA Gain/(Loss)')>0
OR instr([Dim FMS Heirarchy].[FMS Reporting Hierarchy].currentmember.member_caption,'34-Credit Life & Insurance Fees')>0
OR instr([Dim FMS Heirarchy].[FMS Reporting Hierarchy].currentmember.member_caption,'35-Merchant Card Revenue')>0
OR instr([Dim FMS Heirarchy].[FMS Reporting Hierarchy].currentmember.member_caption,'36-Other Fee Revenue')>0
// AND instr([Dim FMS Heirarchy].[FMS Reporting Hierarchy].currentmember.member_caption,'Income Statement')>0
)
        ,LEAVES
    )
);
    This = [Measures].[FMSA Monthly Activity] * (-1);
END SCOPE;

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Bill Anton SSCrazy Points: 2417 More actions · Answer 1

jasonclements32304 - Friday, January 20, 2017 12:07 PM
CALCULATE;
SCOPE(
    DESCENDANTS (
        FILTER(<blah blah blah>>
,
        ,LEAVES
    )
);
    This = [Measures].[FMSA Monthly Activity] * (-1);
END SCOPE;

looks like you're missing an argument in the DESCENDANTS function - which in this case can simply be blank as you're specifying the LEAVES flag.

and yes, this approach will work (depending on the specific logic requirement - e.g. how does it need to aggregate ) but performance will likely be poor.
Another option would be to create a separate column/attribute and moving the FILTER/INSTR logic into the ETL ahead of time allowing you to scope on a single member? Or even a separate physical column in the fact table with a base.

Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com