Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
datadonuts
Helper II
Helper II

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors