ADDCOLUMN() and SUMMARIZE()?

[Mark WalterParticipant Started: 9Replies: 19Total: 28]

I’m curious if there is value in learning ADDCOLUMN and/or SUMMARIZE in DAX for PowerPivot.  Do these functions have any use with tables and pivot?

From what I see written about these two functions, they seem to be used in SQL to build tables.

 

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Hello Mark,

Yes, I have used both of them with DAX measures.

In your Power Pivot experience, eventually you will find yourself boxed in with a challenge and one of those functions will be your key out.

SUMMARIZE is also used to create linkback and reverse linkback tables which you can read about here:

http://www.sqlbi.com/articles/linkback-tables-in-powerpivot-for-excel-2013/

 

[Mark WalterParticipant Started: 9Replies: 19Total: 28]

Okay, I am trying to add a column in a measure to bring back the ‘year’ in the attached example.

I get an error referring to multiple columns?  I got this example from SQLBI and am not sure why this is not working.

Thanks,

 

 

 

Attachments:

You must be logged in to view attached files.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Mark,

AddColumns and Summarize in Power Pivot are used to create intermediate tables for other functions that accept a table as a parameter such as CountRows, SumX, etc.

Because they return tables and not cell (scalar) values, they are not used to output directly to Power Pivot.

The DAX reference says it better than me:

https://technet.microsoft.com/en-us/library/gg492204(v=sql.110).aspx
https://technet.microsoft.com/en-us/library/gg492171(v=sql.110).aspx

If you can paste a link to the SQLBI post for the formula you used, I will follow-up with a comment from my understanding of what the authors had in mind (the men behind SQLBI, Marco Russo and Antonio Ferrari, have achieved Maestro status and I am just a moderator).

Hope this helps.

[Mark WalterParticipant Started: 9Replies: 19Total: 28]

That is very helpful..  Somehow I missed the point that these were used in intermediate tables.  This makes more sense now.. Thank you!

The link below led me to believe that I could simply replicate a calculated column in a measure/calc field.

http://www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/

The first example is what led me to believe I could simply use a measure to calculate the YEAR from the date column, then use that new column in my Pivot.

Thanks for explaining this to me.. sometimes I feel a little DAX dumb 🙂

[Author]

Posts