Issue with changing the filter-context in calculated table

[freddy_b]

Hey there Power-BI community!

I have a general question regarding the use of calculated tables inside a measure defintion which arose as i was playing with the example-dataset in Power-BI-Desktop. The data-model consists of a fact-table called 'financials' and a date-table called 'Calendar'. They are linked via a 1:*-relationship on the date-columns. 

The calender-table 'Calendar' itself is a calculated table which was created as follows:

Calendar = 
VAR cal= CALENDARAUTO()
VAR res = ADDCOLUMNS(
    cal,
    "Year",
    YEAR([Date]),
    "Month Number",
    MONTH([Date])
)
RETURN res

Just for testing my understanding i created the following measure [Sales of October]:

Sales of October = 
VAR summarizedTable = SUMMARIZE(
    'financials',
    'Calendar'[Month Number],
    "Sum of Sales",
    SUM(financials[ Sales])
)

VAR result = CALCULATE(
    SUMX(
        summarizedTable,
        [Sum of Sales]
    ),
    FILTER(
        summarizedTable,
        [Month Number] = 10
    )
)

RETURN result

Calculating the measure [Sales of October] against the Calendar'Month Number' column  gives the following result:

The measure can be described as follows:

1. The variable "summarizedTable" is a calculated table which lists the month-number against the corresponding sum of sales of this specific month. However since there is a filter-context present when using the SUMMARIZE-function i expect summarizedTable to have just a single row when the measure is used against the 'Month Number' column of the 'Calendar'-table. This row consists of the currently selected month number with the corresponding sum of sales of that month.
2. The variable "result" is calculated in a modified filter-context due to the use of CALCULATE. The FILTER-function is iterating the 'summarizedTable' (which to my understanding should consist of just a single row as described in the previous point). If this row is having a Month-Number of 10 (october) then nothing happens. Otherwise the returned value of the FILTER-function should be a blank table.
3. The Sum over the 'Sum of Sales' column should return the sum of Sales for the month october only if 'Month Number' (in the visual) is equal to 10. Otherwise it should return 0/Blank.

However the result is that for each month in the visual the corresponding sum of sales for the specific month gets calulated. It seems as if the filter-context hasn't been changed through the use of CALCULATE at all.

However if i create the summarized-table as a calculated table outside of the measure-definition, and the measure references this calculated-table, then the correct result gets computed:

Definition of the calculated table 'Table with Sales by Month':

Table with Sales by Month = 
SUMMARIZE(
    'financials',
    'Calendar'[Month Number],
    "Sum of Sales",
    SUM(financials[ Sales])
)

Measure definition [Sales of October with calculated table outside of the measure defintion]:

Sales of October with calculated table outside of the measure definition = 

VAR result = CALCULATE(
    SUMX(
        'Table with Sales by Month',
        [Sum of Sales]
    ),
    FILTER(
        'Table with Sales by Month',
        [Month Number] = 10
    )
)

RETURN result

Result:

Note: The measure computes the Sum of Sales for the month october regardless of the row in the visual, since when 'Table with Sales by Month' was created there was no filter-context present. Thus when changing the filter-context by the use of CALCULATE the sum of Sales for october gets calculated regardless of the current row/filter-context in the visual.

My general question is: why does CALCULATE change the filter context when the calculated table has been created outside of the measure-definition, but it doesn't change the filter-context when it has been created inside the measure-definition.

This is just a theoretical question and i was wondering if someone might help me sharpening my understanding.

PS: this is my first post, so i apologize in advance if my question is not properly formated. Thanks in advance for your kind replies!

Kind regards,

Freddy

[audreygerred]

Hi! These articles from Marco Russo and Alberto Ferrari at SQLBI may help to clear things up: Introducing DEFINE COLUMN in DAX queries - SQLBI and Introducing DEFINE TABLE in DAX queries - SQLBI