SCOPE, in the MDX cube script, deals with it by getting the quarter value and dividing it in three, and assigning this new value to each of the months belonging to that quarter.

This is because DimTime is at the daily level or granularity when Fact Sales Quota relates to DimTime, it assigns values on a specific date – however, as these quota values have been defined by the business at the quarter level, the regular sum aggregation at the monthly level does not make much sense. Notice the sales amount quota is tied to only one month. When pivoting the data, without any DAX expressions defined, we get: Note that I created a hierarchy with custom fiscal year (FY) and fiscal quarter (FQ) calculated columns, in order to match the dimension member caption in the Adventure Works cube. To begin with, we load the appropriate tables in the tabular model: We can achieve the same behavior on a tabular model by iterating through the distinct list of months in the lookup (dimension) table. When browsing in Excel, we see the output of the behavior as: In the cube designer we see that this is necessary in order to have monthly values, as the dimension usage has been defined by tying the ‘Sales Targets’ measure group with the Date dimension at the Year/Quarter level of granularity:

In the Adventure Works cube, there is a SCOPE statement in the MDX cube script in which months are evenly assigned the sales amount quota of their parent quarter on the fiscal year 2002: This is the same technique can also be used to solve advanced Last Non Empty scenarios, something we will blog about soon. My colleague Jason Thomas ( blog | twitter ) recently posted a blog entry of a technique in which the expression scope is changed in order to iteratively prototype the correct granularity needed for a business calculation (See his blog entry here). One of these examples is the ability to manipulate the granularity at which a computation executes. But if we examine the topic a bit closer, we realize that a lot of what is possible with SCOPE statements can also be replicated with DAX expressions. It is generally understood you can’t use SCOPE-type functionality in an SSAS Tabular model. With it, one can override cell values at any level of granularity in an SSAS cube and their new custom values will now be considered when returning aggregate results. One of the most powerful MDX functions is SCOPE.

Ssas tabular dax user role