Measurement filtering (or removal of excesses)

Question

Let's say that I have a measure foo,, in a cube, and I have a requirement for reports that users want to see in the report the following measures:

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

What is the best way to handle this? I used to add Named Calculations that return NULLif foo > 10or just foootherwise. I feel that there must be a way to do this in MDX (something like Filter([Measures].[foo], [Measures].[foo] > 10)), but I can’t understand something for me).

Any ideas?

Answer 1

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

, " " Microsoft, MDX , 2000 .

SELECT Filter([Store].[Stores].[Store].members, [Unit Sales] > 2000) ON COLUMNS,
[Unit Sales] ON ROWS
FROM [Warehouse and Sales]

Answer 2

, :

1- ( , ), :

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

, . (, Range_Dimension datasourceview:     1       0

, , .

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

, When, , . ( )

2- , Sale_id. Sale_id dimension Usage tab new dimension measure group Fact mdx - :

filter([dim Sale].[Sale Id].[Sale Id].members,[Measures].[Unit Price]>2000)

Answer 3

saiku (backend with Mondrain), " ", , .

In Saiku3.8, you can add a filter to the user interface: "column" → "filter" → "custom", then you can see the MDX filter expression.

Suppose we want the clicks in an ad to be greater than 1000, and then add the following line:

[Measures].[clicks] > 1000

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

MDX like below (suppose dt as a dimension and clicks as a measure, we want to find dt with clicks over 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]