Filter Dimension values

Question

I have a master Customer dimension (that I don't maintain). My fact table includes customer info. When I compile the cube, everything looks good, by browsing the cube I see my fact rows with only customers that have measure values.

What I'm trying to do should be fairly simple, but I'm stuck. All I want to do is filter my dimension so that it only displays valid customers.

For example, in Excel I can select Customer name and my fact values and I see the list of customers that contain values, but when I click on the filter option of the Customer list, I see all customer, even invalid. I only want to see valid customers!

There's a IsValid flag in the Customer dimension that I'd like to use to only show valid customers.

So, how do I filter the Customer dimension list to only show customers where IsValid =true? MDX query?

Any help would be appreciated.
Thanks, -Alex

mmarie mmarie · Accepted Answer · 2013-06-25T03:03:18

Here is an MDX query that should return the desired answer:

SELECT [Measures].[Measure 1] ON 0,
EXISTS(
[Customer].[Customer Name].[Customer Name].MEMBERS
, [Customer].[Is Valid].[True]
, "Measure Group") ON 1 
FROM [Cube]

Replace [Measures].[Measure 1] with the values you want to see and "Measure Group" with the name of the Measure Group that you want to filter by.

The query returns the Customer Names and associated values from [Measure 1] where [Is Valid] is TRUE that have associated values in "Measure Group".

The Measure Group argument is optional, so you can just get the list of valid Customer Names regardless of if those customers have a related measure if you leave off the last argument.

Link to Exists MDX function info: http://technet.microsoft.com/en-us/library/ms144936.aspx

You could also use [...]

Filter Dimension values

2 Answers