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:
Leave a Reply