cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
datadonuts
Advocate I
Advocate I

CALCUALTE COLUMN + FILTER versus ADDCOLUMNS + FILTER wrong result

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] )
)

 

Screenshot 2020-10-02 141913.jpg

 

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!!!

 

 

 

1 ACCEPTED SOLUTION
AntrikshSharma
Community Champion
Community Champion

@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.

 

View solution in original post

1 REPLY 1
AntrikshSharma
Community Champion
Community Champion

@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.

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors