I have a MDX query that return result like this:
Customer | Store | Sales
A | Store_1 | 123.45
A | Store_2 | 234.56
B | Store_2 | 345.67
B | Store_3 | 456.78
C | Store_1 | 543.21
C | Store_3 | 654.32
How the query should look to have result that show only one row per customer with the first store only:
A | Store_1 | 123.45
B | Store_2 | 345.67
C | Store_1 | 543.21
Update: Actual query
SELECT
[Measures].[Sales] ON COLUMNS,
NON EMPTY(
[Customers].[User].[User], [Stores].[Store].[Store]
) ON ROWS
FROM SALES
If I run this:
SELECT
[Measures].[Internet Sales Amount] ON 0
,
[Product].[Category].[Category]
*
[Customer].[Customer Geography].[Country] ON 1
FROM [Adventure Works];
I get the following:
If I just want the first
for each category I need to do something like this:
WITH
SET [cats] AS
[Product].[Category].[Category]
SET [cats_country] AS
Generate
(
[cats] AS S
,
S.Current
*
Head(NonEmpty([Customer].[Customer Geography].[Country],S.Current))
)
SELECT
[Measures].[Internet Sales Amount] ON 0
,[cats_country] ON 1
FROM [Adventure Works];
Results in this: