MDX Query

[MSuser5]

Hi Team,

I would like to calculate sum of value by excluding 'null' values in the column, I have tried in SSAS cube-new MDX as like below but the sum result is wrong instead of 9845  result is : -5662 (coming as negative and wrong value)

my MDX (sum of wait time in minutes) : (sum([Measures].[Wait Time In Sec])/sum([Measures].[Total]))/60

kindly suggest or correct me where i missed .

Thanks,

[cpwebb]

I don't think the nulls are the problem - I don't see how they would result in a negative value here. What type of aggregation is your waitTimeInSec measure using? As I said, if you have already built a measure then writing something like sum([Measures].[Wait Time In Sec])  is redundant and [Measures].[Wait Time In Sec] should give you the same incorrect result.

[MSuser5]

Hi @cpwebb ,

i too wondering about result eventhough [Wait Time In Sec] is int datatype and i created only one measure which is used sum function.

sum([Measures].[Wait Time In Sec])

Thanks,

[cpwebb]

In MDX (I assume you're using SSAS Multidimensional) using the SUM function with a measure in the way you're doing here doesn't actually have any effect - the default aggregation behaviour of the measure will be used. So using the following MDX in a measure:

([Measures].[Wait Time In Sec]/[Measures].[Total])/60

should give you the same incorrect result. What's more, MDX automatically ignores null values while aggregating anyway. Can you provide more details - with sample data - of what you want to do here?

More complex aggregation behaviour is possible using SCOPE statements. The following blog post might help:

https://blog.crossjoin.co.uk/2013/05/29/aggregating-the-result-of-an-mdx-calculation-using-scoped-as...

Chris

[MSuser5]

hi @cpwebb ,

yes i'm using SSAS multidimentional table here i tried to create new calculated measure like below 

MDX (sum of wait time in minutes) : sum([Measures].[Wait Time In Sec])

here is my table:

Wait Time In Sec	Total
58896	5568
56689	4869
NULL	9562
96347	7856
77546	4489
NULL	8965
89647	7854

Expected total  of waitTimeInSec = 379125

i'm not sure may be due to NULL values result is coming : -266545 ( as negative)

kindly suggest how to create measure with replacing NULL values as 0  and sum of total.

Thanks,

MS

[cpwebb]

Is waitTimeInSec itself a calculated measure? If so, what is the definition? I don't think the nulls are the problem here.

Chris

[MSuser5]

Hi @cpwebb ,

i have created measure in SSAS Cube level referring fact table column  (which is waitTimeInSec)

so the measure result while SUM appear wrongly as negative total. Is this because of NULL on that column?

MS

[cpwebb]

Like I said, I don't think the nulls are the problem. You will need to provide a lot more detail on your cube design (please post screenshots of all relevant properties etc) in order to troubleshoot this.

[MSuser5]

Hi @cpwebb ,

Sure let me do deep analaysis on this issue and will share the details soon.

Thanks for your prompt response.

Regards,

MS

[MSuser5 · Accepted solution]

Thanks @cpwebb ,

I have used SUM(coalesceEmpty([Measure].[waitTimeInSec],0)) and it's working fine.