Forum Discussion

MSuser5's avatar
MSuser5
Helper III
2 years ago
Solved

MDX Query

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 : ...
Need Help
  • MSuser5's avatar
    MSuser5
    2 years ago

    Thanks cpwebb ,

     

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

cpwebb's avatar
cpwebb
Microsoft Employee
2 years ago

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-assignments/

 

Chris

 

MSuser5's avatar
MSuser5
Helper III
2 years ago

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 SecTotal
588965568
566894869
NULL9562
963477856
775464489
NULL8965
896477854

 

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's avatar
    cpwebb
    Microsoft Employee
    2 years ago

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

     

    Chris

    • MSuser5's avatar
      MSuser5
      Helper III
      2 years ago

      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's avatar
        cpwebb
        Microsoft Employee
        2 years ago

        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.