使用“父”CurrentMember筛选条件

以下是数据集:

CREATE TABLE Movies(id INT, name VARCHAR(50), genre VARCHAR(50), budget DECIMAL(10));
INSERT INTO Movies VALUES
    (1, 'Pirates of the Caribbean', 'Fantasy', 379000000),
    (2, 'Avengers', 'Superhero', 365000000),
    (3, 'Star Wars', 'Science fiction', 275000000),
    (4, 'John Carter', 'Science fiction', 264000000),
    (5, 'Spider-Man', 'Superhero', 258000000),
    (6, 'Harry Potter', 'Fantasy', 250000000),
    (7, 'Avatar', 'Science fiction', 237000000);

要相对过滤到一个恒定值没有问题,例如,要获得预算高于3亿美元的所有电影:

WITH
    MEMBER X AS SetToStr(Filter(Movie.[Name].[Name].Members - Movie.[Name].CurrentMember, Measures.Budget > 300000000))
SELECT
    Movie.[Name].[Name].Members ON ROWS,
    X ON COLUMNS
FROM
    Cinema

它给出:

Avatar          {[Movie].[Name].&[Avengers],[Movie].[Name].&[Pirates of the Caribbean]}
Avengers        {[Movie].[Name].&[Pirates of the Caribbean]}
Harry Potter    {[Movie].[Name].&[Avengers],[Movie].[Name].&[Pirates of the Caribbean]}
John Carter     {[Movie].[Name].&[Avengers],[Movie].[Name].&[Pirates of the Caribbean]}
Pirates of the Caribbean    {[Movie].[Name].&[Avengers]}
Spider-Man      {[Movie].[Name].&[Avengers],[Movie].[Name].&[Pirates of the Caribbean]}
Star Wars       {[Movie].[Name].&[Avengers],[Movie].[Name].&[Pirates of the Caribbean]}

但是,如何与当前电影的预算相比,而不是硬编码的3亿美元,让电影比当前电影更贵?

它会给 {} 因为《加勒比海盗》是最昂贵的电影。
对于《复仇者》来说是这样的 { 'Pirates of the Caribbean' } 因为这是第二贵的,只有《加勒比海盗》更贵。
对于《阿凡达》,它会给所有其他电影,因为它是比较便宜的。

问题在于 Filter 函数的条件 CurrentMember 指当前测试的元组,而不是当前在 ROWS 轴