Filter PowerPivot/MDX Query

[bauer24]

Hi all,

I'm new to PowerPivot but have got as far as easily using a design interface that writes the necessary MDX query that then pulls my data.

My question: My query returns a large dataset with just one "metric" column (ROI Value). A lot of these rows have 0 for ROI Value, and so I don't really need them. How can I filter my query so only rows with ROI Value > 0 remain? I don't want to filter within PowerPivot, I want the query to return just non zero rows.

My current query is like so:

SELECT NON EMPTY { [Measures].[ROI Value] } ON COLUMNS, NON EMPTY { ([Advertiser].[Advertiser].[Advertiser].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [HM_BI_CUBE]

I imagine it invloves changing the first bit SELECT NON EMPTY { [Measures].[ROI Value] }

 

[powerpivotpro]

I'm out of my depth on this one - I learned PowerPivot so that I could avoid learning MDX

I must say though that in my experience, 90% of the time someone is importing data from a cube (into PowerPivot), they would be better off importing data from the underlying "flat" data sources that the cube is based on. Where "better off" means "you retain the richness of possibilities in PowerPivot," whereas importing from the cube starts you off at a much coarser level of detail.

There are certainly valid cases to import from a cube of course, but I always counsel my clients to consider the other possibility.