Cannot find rows equal to some number value in MDX filter function.

I had problem getting a particular row from my query when using the filter function with “=”.

I wanted to get a row where a particular column had a particular value.

select {[Measures].[Internet Sales Amount]} on columns,
filter([Date].[Calendar].[Calendar Year].members,
[Measures].[Internet Sales Amount] > 0) on rows

tut2_resultFilterGreaterThanZero

Now if I want to show the row that´s marked in picture I would change my query to:

select {[Measures].[Internet Sales Amount]} on columns,
filter([Date].[Calendar].[Calendar Year].members,
[Measures].[Internet Sales Amount] = 6530343.53) on rows

But then I only got empty result..

After som research I found out that it had something to do with the format in which the [Internet Sales Amount] had. In my case it was in money and could be rounded from 6530343.53000001 to 6530343.53. That´s why it didn´t said it was equal.

My solution was to use the round function on the [Internet Sales Amount] before applying it to the filter function:

select {[Measures].[Internet Sales Amount]} on columns,
filter([Date].[Calendar].[Calendar Year].members,
Round([Measures].[Internet Sales Amount],2) = 6530343.53) on rows

And then I got my desired row:

tut2_resultFinalRow

Advertisements
Tagged with: , , , , ,
Posted in MDX

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archive
Categories

Blogstatistics
  • 3,777 hits
Boyan Penev on Microsoft BI

This is my journey towards greater knowledge with Microsofts Business Intelligence Solutions.

Sherry's BI Corner

All about Microsoft’s BI Platform (SQL Server, Reporting Services, Analysis Services, Integration Services)

Sherry's BI Corner

This is my journey towards greater knowledge with Microsofts Business Intelligence Solutions.

Comments for CALUMO

This is my journey towards greater knowledge with Microsofts Business Intelligence Solutions.

True Tiger Recordings

This is my journey towards greater knowledge with Microsofts Business Intelligence Solutions.

Data Inspirations

This is my journey towards greater knowledge with Microsofts Business Intelligence Solutions.

%d bloggers like this: