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]
[Claim].[Claim Number].[Claim Number],
IIF( (abs(round([Measures].[ITD CAse Loss Amount] ,3))>=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?