Count members based on aggregated measure SSAS MDX

kiran l 21 Reputation points

I want to count # of claims(Claim dimension) based on a measure(Case Loss). I have a transactional Fact table with the measure(Case Loss), but the value of the measure to be used in the condition has to be at the claim level and not the transaction level. I tried to add the calculation in the DSV but it takes a longer time to process the cube as the query gets complex. I am trying to resolve this the MDX way.

I already have MDX which resolves this but it takes a longer time to show results. The count - filter mDX is slowerthan sum MDX
CREATE MEMBER CURRENTCUBE.[Measures].[ClaimCount_Incurred]
AS Sum(
[Claim].[Claim Number].[Claim Number],
IIF( (abs(round([Measures].[ITD CAse Loss Amount] ,3))>=1)
, 1

I am trying to find a way better way to get the results in an efficient manner. Is there a way to solve this using a dummy dimension?

Update: We also have a distinct claim count in the cube, performance is really good with any query. Could I use the distinct count to get the conditional count?


SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,206 questions
0 comments No comments
{count} votes

Accepted answer
  1. Alexei Stoyanovsky 3,411 Reputation points

    You could try declaring the abs(round(.. part as one or two hidden helper measures, which might help the engine, and double-check that the bulk mode is actually used, but this MDX is quite optimized.
    One alternative approach is to create a measure group off the Claims table, which will add some M2M relationships to your cube but could be faster.
    Note also that query response time is usually more important than processing time.

2 additional answers

Sort by: Most helpful
  1. Lukas Yu -MSFT 5,806 Reputation points


    How much rows and how long time are you talking here ?

    I think your expression is fairly clean and does not leave much space for optimization. I think probably is that there are large amount of rows so it costs a long time for this ?


  2. kiran l 21 Reputation points

    So finally this has worked for me: Moving the entire business logic to the cube is the best way to go as suggested by @Alexei Stoyanovsky but I had to skip it due to the complexity of the code. I made few changes to the MDX code and moved part of the logic to the Cube. I also made sure the NULL values are preserved in the cube so that helped too. Not including the empty claims numbers in the calculation sped up the results
    Following is my MDX code: The time was reduced from 7mins to 1 min which is a step in the right direction.

    SQL Code:
    CASE WHEN NatureTypeKey = 'C' THEN NULL
    WHEN (abs(round([Case Reserve], 3)) >= 1) THEN NULL
    ELSE [Case Reserve] END AS [Gross Case Loss and ALAE Reserve Closed]

    CREATE MEMBER CURRENTCUBE.[Measures].[Gross_ClaimCount_Closed]
    AS Sum( nonempty([Claim].[Claim Number].[Claim Number], [Measures].[Gross Case Loss and ALAE Reserve Closed]),
    abs(round([Measures].[ITD Gross Case Loss And ALAE Reserve Amount],3))<1
    ) , 1, NULL )),

    Thank you @Alexei Stoyanovsky and @Lukas Yu -MSFT for all the help.
    I appreciate it.