CALCUALTE COLUMN + FILTER versus ADDCOLUMNS + FILTER wrong result

[datadonuts]

Hi everyone,

I went throught the forum and checked lots of posts and documentations regarding ADDCOLUMN vs SUMMARIZE and SUMMARIZECOLUMN, still cannot figure out, why my results come out wrong.

1. Here is the DAX for a simple table using SUMMARIZE COLUMN + FILTER (correct result)

Overdue shipping SUMMARIZECOLUMN + FILTER =
SUMMARIZECOLUMNS (
customers[CompanyName],
FILTER ( fOrdersHead, fOrdersHead[deliverydays] >= 20 ),
"avg_delay", AVERAGE ( fOrdersHead[deliverydays] )
)

2. I tried to achieve the same result with ADDCOLUMNS + FILTER in various ways, but it always comes out wrong! 

Overdue shipping ADDCOLUMNS + SUMMARIZE + FILTER =
ADDCOLUMNS (
SUMMARIZE (
FILTER ( fOrdersHead, fOrdersHead[deliverydays] >= 20 ),
customers[CompanyName]
),
"Overduedays", AVERAGE ( fOrdersHead[deliverydays] )
)

The same example without filtering the table comes out correct in both cases (summarize and add column + summarize).

Thus the problem seems the table filter.

Q1: how to adjust the second DAX with filter to come to the correct result?

Q2: why i have to use ADDCOLUMNS anyhow, respectively in which cases does it make sence to use it instead just simply SUMMARIZECOLUMN?

thanks a lot again!!!

[AntrikshSharma · Accepted solution]

@datadonuts  SUMMARIZECOLUMNS is evaluating AVERAGE in a FILTER CONTEXT, where as ADDCOLUMNS operates on ROW CONTEXT, and AVERAGE is getting evaluated in an empty FILTER CONTEXT therefore you get the same value for all rows, wrap AVERAGE inside CALCULATE and it will work

Q1 - use this:

Overdue shipping ADDCOLUMNS + SUMMARIZE + FILTER =
CALCULATETABLE (
    ADDCOLUMNS (
        SUMMARIZE ( fOrdersHead, customers[CompanyName] ),
        "Overduedays", CALCULATE ( AVERAGE ( fOrdersHead[deliverydays] ) )
    ),
    fOrdersHead[deliverydays] >= 20
)

Q2 - Always, if you are creating a measure and you have to create a new virtual column, always use ADDCOLUMNS/SUMMARIZE construct, SUMMARIZE is fine to group the data but DO NOT use it to add columns as the query plan generated is highly inefficient. 

If you are creating a calculated table then you can rely on just SUMMARIZECOLUMNS as it is more effcient than ADDCOLUMNS/SUMMARIZE construct and generates really efficient queries, you can use DAX STUDIO to verify this.

For measures - SUMMARIZECOLUMNS doesn't work when the filter context contains a filter that was generated by a context transition. Therefore a measure containing SUMMARIZECOLUMNS cannot be used in PBI. To produce a report, Power BI generates DAX queries that iterate over the cells to be populated. This means that measures that contains SUMMARIZECOLUMNS cannot be used to produce a report.