'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 |
|---|
