List all dimension members filtering on dimension attribute value

Question

I want to list out all cars from Dimension.Car which has a status IsPremium = 1 from cube. And also, a separate list of all cars from Dimension.Car which has status IsFourWeeler = 1

Dimension has attributes as follows: 1. Car Code 2. Car Name 3. IsPremium 4. IsFourWheeler

Answer 1

If they are different hierachies (either attribute or user hierarchies) in the same dimension then you can use the function EXISTS.

Defined here: https://msdn.microsoft.com/en-us/library/ms144936.aspx?f=255&MSPPError=-2147217396

The example they show is the same as your situation:

SELECT 
  [Measures].[Internet Sales Amount] ON 0,
  EXISTS(
    [Customer].[Customer].[Customer].MEMBERS
 , {[Customer].[State-Province].&[CA]&[US]}
  ) ON 1 
FROM [Adventure Works];

But you have

SELECT 
  [Measures].[SomeMeasuresInCube] ON 0,
  EXISTS(
     [Dimension.Car].[Car].MEMBERS
  ,  [[Dimension.Car]].[IsPremium].[1]
  ) ON 1 
FROM [YourCube];

NonEmpty could also be your friend:

WITH SET [SpecialCars] AS
  NONEMPTY(
    [Dimension.Car].[Car].MEMBERS
  ,([[Dimension.Car]].[IsFourWheeler].[1])
  )  
SELECT 
  [Measures].[SomeMeasuresInCube] ON 0,
  [SpecialCars]     ON 1 
FROM [YourCube];