5

Say I have a measure, foo, in a cube, and I have a reporting requirement that users want to see the following measures in a report:

total foo
total foo excluding instances where foo > 10
total foo excluding instances where foo > 30

What is the best way to handle this? In the past, I have added Named Calculations which return NULL if foo > 10 or just foo otherwise. I feel like there has to be a way to accomplish this in MDX (something like Filter([Measures].[foo], [Measures].[foo] > 10)), but I can't for the life of me figure anything out.

Any ideas?

Jeff
  • 12,555
  • 5
  • 33
  • 60
Colin
  • 309
  • 2
  • 5
  • 15

3 Answers3

3

The trick is that you need to apply the filter on your set, not on your measure.

For example, using the usual Microsoft 'warehouse and sales' demo cube, the following MDX will display the sales for all the stores where sales were greater than $2000.

SELECT Filter([Store].[Stores].[Store].members, [Unit Sales] > 2000) ON COLUMNS,
[Unit Sales] ON ROWS
FROM [Warehouse and Sales]
Sam Holloway
  • 1,999
  • 15
  • 14
  • 2
    I appreciate your input, that is not at all the desired effect I am going for. Your example filters a set of Stores, but what I am looking for is a way to filter fact records. Using your example, say I wanted to show the average Unit Sales per store, I still want to show every Store, but I don't want the average to aggregate Unit Sales > 2000. – Colin Nov 20 '09 at 19:02
2

I met similar problem when use saiku (backend with Mondrain), as I haven't found any clear solution of "add filter on measure", I added it here, and that may be useful for other guy.

In Saiku3.8, you could add filter on UI: "column"->"filter"->"custom", then you may see a Filter MDX Expression.

Let's suppose we want clicks in Ad greater than 1000, then add the following line there:

[Measures].[clicks] > 1000

Save and close, then that filter will be valid for find elem with clicks greater than 1000.

The MDX likes below (suppose dt as dimension and clicks as measure, we want to find dt with clicks more than 1000)

WITH
SET [~ROWS] AS
    Filter({[Dt].[dt].[dt].Members}, ([Measures].[clicks] > 1000))
SELECT
NON EMPTY {[Measures].[clicks]} ON COLUMNS,
NON EMPTY [~ROWS] ON ROWS
FROM [OfflineData]
linpingta
  • 2,324
  • 2
  • 18
  • 36
  • Thanks for this. It worked perfectly. I was interested to put a filter on a Measures called SoldQty and wanted to allow End user to use this filter in SSRS Report as a Report parameter. WITH SET [~ROWS] AS Filter({[Dt].[dt].[dt].Members}, ([Measures].[clicks] > STRTOVALUE(@SoldQty))) SELECT NON EMPTY {[Measures].[clicks]} ON COLUMNS, NON EMPTY [~ROWS] ON ROWS FROM [OfflineData] – Shivenndoo Apr 15 '20 at 00:25
0

i think you have two choices:

1- Add column to your fact(or view on data source view that is based on fact table)like:

case when unit_Price>2000 then 1 
     else 0  
end as Unit_Price_Uper_Or_Under_10

and add a fictitious Dimension based on this columns value. and add named query for New Dimension(say Range_Dimension in datasourceview : select 1 as range union all select 0 as range

and after taht you cant used this filter like other dimension and attribute.

 SELECT [Store].[Stores].[Store].members ON COLUMNS,
[Unit Sales] ON ROWS
FROM [Warehouse and Sales]
WHERE [Test_Dimension].[Range].&[1]

the problem is for every range you must add When condition and only if the range is static this solution is a good solution. and for dynamic range it's better to formulate the range (based on disceretizing method )

2- add dimension with granularity near fact table based on fact table for example if we have fact table with primary key Sale_id.we can add dimension based on fact table with only one column sale_Id and in dimension Usage tab we can relate this new dimension and measure group with relation type Fact and after that in mdx we can use something like :

filter([dim Sale].[Sale Id].[Sale Id].members,[Measures].[Unit Price]>2000)
abianari
  • 343
  • 7
  • 19