Thank you for the response. Here is the requirement I am working on: In my Direct Measures crosstab, I would like to apply State, Manufacturer filter on all rows except the CustomMeasure1. However, I would still like to filter all rows using OrderDate, Region. Due to which I used FIXED LOD. Thanks for taking time for discussing this.
Nested LoD may be tricky especially if you have outer FIXED and inner EXCLUDE/INCLUDE
As far as I remember those inner expressions are converted to FIXED and granularity is either inherited from the outer one and results either aggregated more or repeated.
As you're using Year of Order Date as a Filter --
actually the calc is DATEPART('year', [Order Date]) --
you'd like to re-write your CustomMeasure1 (and others as well):
FIXED YEAR([Order Date]), [Region] :
SUM(IF [Sub-Category] = 'Accessories'
THEN [Number of Records]
1) With your current calculations we're catching
the following effect (because of sparse data):
-- not every [Sub-Category] is sold
in each [Region] on each [Order Date].
So filtering by either [Manufacturer] or [State] (or both)
may actually remove some datasource rows -- the specific
[Sub-Category],[Region],[Order Date] value combinations --
from being aggregated into the Marks on the view.
2) BTW, even if a FIXED LOD is calculated before a view
(formally at a Row-Level), the outer aggregation -- such as
SUM( [CustomMeasure1] ) -- to be calculated on Marks
after Regular Dimension Filters applied, hence the result.
Hope this could help understanding.