Posts Tagged ‘mdx’

Handling Multiselect Filter problem in Excel with Calculated Measures AVG,MAX etc.

December 18, 2011 4 comments

In my customers most of the users are using Excel2007/2010 for accessing OLAP data.
Maybe someone has already blogged about it but  i want to share my experience.
I will use Adventure Works 2008 Cube in my example.

The article is about MultiSelect Filter problem in Excel.

Lets take  [Internet Sales Amount] as a measure and look at the values in Date.Calender hierarchy.













Now i will write a formula to calculate the AVERAGE Sales Amount.

CREATE MEMBERCURRENTCUBE.[Measures].[Average Internet Sales Amount 1] AS

Avg( Descendants([Date].[Calendar].CurrentMember,[Date].[Calendar].[Month],SELF),[Measures].[Internet Sales Amount])

Now lets test the value i am choosing January, February and March 2008 as Month filter. The value should be $1,427,876.65 as shown above.











ok we saw that [Averagete Internet Sales Amoun 1] is true.

Now lets select January 2008 and February 2008. And look at the Average before filtering it.














The Average is $1,401,362.39

Lets Drag the hierarchy to the filter any select January 2008, February 2008.










 Opps the value is $793,477.88 and it is not true.

We can write a mdx query to see the correct value, but as i sad before the aim to see the right value in Excel.

ok what to do to see the correct value.  We are writing the same formula, but this time i am using SET in my formula.







Lets check the query





We see $1,401,362.39 as [Average Internet Sales Amount 2]. This is the value we expected to see.

Happy New Year 🙂



Analysis Services 2008 R2 Performance Guide

October 11, 2011 Leave a comment

Analysis Services 2008 R2 Performance Guide from sqlcat has just released.

That is the link

Download pdf

Last Ever Non Empty – a new, fast MDX approach (via Chris Webb’s BI Blog)

July 13, 2011 Leave a comment

Last Ever Non Empty - a new, fast MDX approach The last non empty semi-additive measure aggregation functionality in SSAS enterprise edition is very useful, but it doesn’t support one common business requirement: while it will give you the last non empty value within any given time period, it doesn’t handle the variation where you want to get the last non empty value of a measure from all preceding time periods (this is what I’m calling the ‘last ever non empty’ value). There are a number of … Read More

via Chris Webb’s BI Blog

Categories: Sql Server Analysis Services Tags: