CODEWITHSUNDEEP
ssasmdx
prav
prav

Reputation: 450

How to filter measures in MDX

I need to filter the measure values as

MeasureA   MeasureB
 10          10
 15          15
  5          20
 20          20

Here I need to get only the measures are not equal, I am using filter function as but not working

Select Filter({[Measures].[A], [Measures].[B]}, ([Measures].[A]- [Measures].[B])=0) on 0 from [Cube]

Expected result set 

    MeasureA   MeasureB     
     5          20

What am I missing?

Upvotes: 0

Views: 6013

Answers (2)

Bill Anton
Bill Anton

Reputation: 2970

You might want to try creating a calculated field in the DSV for this Fact table...

CASE
  WHEN MeasureFieldA != MeasureFieldB THEN 1
  ELSE 0
END

Then you can create a "fact dimension" and have this calculated field as an attribute to be used in your queries or calculated measures.

Upvotes: 0

Peter
Peter

Reputation: 634

Try using a dimension instead of your measures for the first part of the filter statement. Assuming you are querying products then your query might look like:

select {[Measures].[A],[Measures].[B]} on columns,
filter ({[Products].Members},[Measures].[A] = [Measures].[B]) on rows
from [Sales Cube]

Upvotes: 4

Related Questions