CODEWITHSUNDEEP
filterequalsmdx
ATB
ATB

Reputation: 31

MDX Filter Query

I have a query that is trying to filter the results where it equals a certain value. The query belows works fine...

SELECT NON EMPTY {[Date].[Year Number].Members} ON COLUMNS, 
NON EMPTY CROSSJOIN({[ItemCode].[Item Category].Members}, {[Measures].[Net Sales], [Measures].[Total Cost], [Measures].[Profit]}) ON ROWS  
FROM (SELECT Filter([ItemCode].[Item Category].[Item Category].Members, ([Measures].[Net Sales] = 3440.8)) ON COLUMNS FROM [Sales])

...however an identical query but with a different value...

SELECT NON EMPTY {[Date].[Year Number].Members} ON COLUMNS, 
NON EMPTY CROSSJOIN({[ItemCode].[Item Category].Members}, {[Measures].[Net Sales], [Measures].[Total Cost], [Measures].[Profit]}) ON ROWS  
FROM (SELECT Filter([ItemCode].[Item Category].[Item Category].Members, ([Measures].[Net Sales] = 41581.65)) ON COLUMNS FROM [Sales])

...doesnt want to work at all. I know they both should work fine as both values exist in the cube, and it isnt just this value that doesnt work, it is quite a few, and as far as I can see there is no pattern.

Upvotes: 3

Views: 772

Answers (1)

Craig
Craig

Reputation: 1337

I'd suggest checking the data type for [Net Sales] if it's a floating point type e.g. Double then searching for an exact value may not work. i.e. the 41581.65 value may be 41581.6500001 in the database.

If you change the data types to fixed precision e.g. Currency then the queries may work. Have a look at this link for the many benfits of money

Upvotes: 2

Related Questions