MDX Filter and Sum

_{Tuesday, August 1, 2017 10:12 PM}

Hello All,

Here is my situation:

I have following measures:

1. [Open Claims] (having values 0, 1, 2 etc.) – This is number of open claims at that point of time. This is derived from claim inventory table that is related to claim table on claim key.
2. [sum of amounts] - This is derived from fact table having claim key

So there are three tables here – Claim, Claim Inventory and Fact (all related on claim key)

Claim Inventory is a kind of fact table not dimension.

I want to create a measure [Total Open Claims Amount] – This is the sum of amounts when [Open Claims] > 1

Logical steps -  

First filter all the claim numbers that has open claims = 1 (in the selected scope)

Calculate [sum of amounts] for that.

This is not giving me correct results:

CREATE MEMBER CURRENTCUBE.[Measures].[Total Open Claims Amount]

AS

SUM (

        FILTER([Claim].[Claim Number].Members, [Measures].[Open Claims] > 0),

        [Measures].[sum of amounts]

    )

I want to write this in MDX, This is the DAX syntax that works perfectly fine - CALCULATE([sum of amounts], FILTER( 'Claim', [Open Claims] > 0))

_{Wednesday, August 2, 2017 1:17 AM ✅Answered}

Hi Itz Shailesh,

Thanks for your question.

[Claim].[Claim Number].Members including all member [Claim].[Claim Number].[ALL]. In your scenario, you may replace [Claim].[Claim Number].Members with [Claim].[Claim Number].[Claim Number].Members. See below sample MDX expression:

CREATE MEMBER CURRENTCUBE.[Measures].[Total Open Claims Amount]
AS
SUM (
        FILTER([Claim].[Claim Number].[Claim Number].Members, [Measures].[Open Claims] > 0),
        [Measures].[sum of amounts]
    )

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

_{Wednesday, August 2, 2017 1:50 PM}

Thank you willson and Alexei. You saved my day... Cheers!!!