0

I have a FactBudget per sales region with fields [Budget SalesRegion] and [Sales Region]. In the dimension DimCustomer I have the fields [Sales Region] and [Customer Type].

The relation between FactBudget and DimCustomer is defined only on the [Sales Region].

I have a rather simple mdx statement that filters the [Sales Region] of the DimCustomer for a certain [Customer Type]. With the returned [Sales Region] I select the correspondent [Budget SalesRegion]:

SELECT 
   (
   FILTER(
      [Kunde].[Sales Region].members
     ,[Kunde].[Customer Type].CURRENTMEMBER
          =[Kunde].[Customer Type].[All].[Direct Sales]
    )
   ,[Measures].[Budget SalesRegion]
   ) ON 0,
   [Kunde].[Customer Type].[All].[Direct Sales] ON 1
FROM [BI_DWH];

How can I translate this statement into an Calculated Member in the SSAS cube so that the selected [Customer Type] in a slicer filters the statement accordingly?

JAL
  • 41,701
  • 23
  • 172
  • 300
baeschti
  • 54
  • 6

1 Answers1

0

This:

SELECT 
   (
   FILTER(
      [Kunde].[Sales Region].members
     ,[Kunde].[Customer Type].CURRENTMEMBER
          =[Kunde].[Customer Type].[All].[Direct Sales]
    )
   ,[Measures].[Budget SalesRegion]
   ) ON 0,
   [Kunde].[Customer Type].[All].[Direct Sales] ON 1
FROM [BI_DWH];

Is the same as this:

SELECT 
   [Measures].[Budget SalesRegion] ON 0,
   [Kunde].[Customer Type].[All].[Direct Sales] ON 1
FROM [BI_DWH];

Or if you want to use the slicer axis:

SELECT 
   [Measures].[Budget SalesRegion] ON 0
FROM [BI_DWH]
WHERE [Kunde].[Customer Type].[All].[Direct Sales];

Now it is simplified I do not understand what you question is?

whytheq
  • 34,466
  • 65
  • 172
  • 267
  • No, it's not the same. As stated in my question, the `[Budget SalesRegion]` is only related to `[Kunde]` by the field `[Sales Region]`. With your proposed statement, I just get the total of `[Budget SalesRegion]`. There is not relation between the `[Budget SalesRegion]` and `[Kunde].[Customer Type]`. I need to filter `[Kunde]` by `[Customer Type]`, get the `[Sales Region]` from this selection, and then get my `[Budget SalesRegion]` from the selected `[Sales Regions]`. – baeschti Sep 17 '15 at 11:00