'How to Filter depending on the other members of the dimension?

The dataset:

CREATE TABLE Demography(id INT, continent VARCHAR(50), country VARCHAR(50), city VARCHAR(50), population DECIMAL(10));
INSERT INTO Demography VALUES
    (1, 'Asia', 'Japan', 'Tokyo', 37000000),
    (2, 'Asia', 'India', 'Delhi', 29000000),
    (3, 'Asia', 'China', 'Shanghai', 26000000),
    (4, 'America', 'Brazil', 'Sao Paulo', 22000000),
    (5, 'America', 'Mexico', 'Mexico City', 22000000),
    (6, 'Africa', 'Egypt', 'Cairo', 20000000),
    (7, 'Asia', 'India', 'Mumbai', 20000000),
    (8, 'Asia', 'China', 'Beijing', 20000000),
    (9, 'Asia', 'Bangladesh', 'Dhaka', 20000000),
    (10, 'Asia', 'Japan', 'Osaka', 19000000),
    (11, 'America', 'United States', 'New York', 19000000),
    (12, 'Asia', 'Pakistan', 'Karachi', 15000000),
    (23, 'America', 'United States', 'Los Angeles', 12000000),
    (24, 'Europe', 'Russia', 'Moscow', 12000000),
    (28, 'Europe', 'France', 'Paris', 11000000),
    (44, 'Africa', 'Angola', 'Luanda', 8000000),
    (79, 'Africa', 'Egypt', 'Alexandria', 5000000),
    (1000, 'Antartica', NULL, NULL, NULL);

A single dimension Geography with 3 attributes hierarchies: Continent, Country, and City.
A single measure: Population.

How to filter the cities with a condition depending on other cities of the same country:

  • only cities which are the single one of their country
  • only cities in countries with more than a certain number of inhabitants...

It should be something like:

SELECT
    Filter(City.Children, Count(???) = 1) ON ROWS,
    -- Filter(City.Children, (???, Population) >= 50E6) ON ROWS,
    Population ON COLUMNS
FROM
    Demography

My best try is this one:

SELECT
    Filter(City.Children, Count(Extract(City.CurrentMember * Country.Children, Country) * City.Children) = 1) ON ROWS,
    Population ON COLUMNS
FROM
    Demography

The idea is:

  • get the city's country: Extract(City.CurrentMember * Country.Children, Country)
  • get the country's cities: * City.Children

But it returns nothing as Extract is generating an empty set if my investigations are correct.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source