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

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:

Advertisements

‹ New learning material

Working with parameters in SSRS from SSAS cubes. ›

Tagged with: Equal, Filter, MDX, Result, Round, Row
Posted in MDX