Search code examples
ssasmdx

SQL subselect, group by, sum equivalent in MDX(SSAS)

I have a problem with creation of calculated member in SSAS. Fact table:

  Date       Store       Product        Sales_in_USD
    --------------------------------------------------
    2016-07-01 Store1      Product1       50
    2016-07-01 Store1      Product2       100
    2016-07-01 Store2      Product3       70
    2016-07-01 Store2      Product2       85

Dimensions: Dates,Stores,Products

I want to get something like that: If I filter by some product I want to get all sales by this store and date that include sales by filtered product+another products, for example I want filter by Product1:

SQL code:
select sum(Sales_in_USD)
from [Fact table]
where Store in (select Store from [Fact table] where Product="Product1")

Executing this sql code I get all Sales by Store1.

How I can create it with MDX when I want create a calculated member?

Output of calculated member must be the next:

Product   Total_Sales_By_Store
------------------------------
Product1  50+100=150
Product2  50+100+70+85=305
Product3  70+85=155
Solution

  • 1 - Determine the valid stores for the selected product.

    NonEmpty(
    [Store].[Store Name].MEMBERS,
    ([Product].[Product].Currentmember,[Measures].[Sales_in_USD])
)

    2 - Once you have the stores for the current product, you want to calculate the sum of values for the possible tuples(cross join).

    with member Measures.[Total Sum of Stores] as
sum(
[Product].[Product].Currentmember *
NonEmpty
   (
    [Store].[Store Name].MEMBERS,
    ([Product].[Product].Currentmember,[Measures].[Sales_in_USD])
   )    
,
[Measures].[Sales_in_USD]
)

select Measures.[Total Sum of Stores] on 0
from [YourCube]
where [Product].[Product].[Product1]