Home › Forums › Power Pivot › ADDCOLUMN() and SUMMARIZE()?
Tagged: dax
This topic contains 4 replies, has 2 voices, and was last updated by Mark Walter 8 years, 7 months ago.
-
AuthorPosts
-
August 6, 2015 at 1:44 pm #1324
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.
August 6, 2015 at 2:20 pm #1325Hello 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/
August 8, 2015 at 1:08 am #1341Okay, 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.August 8, 2015 at 2:31 pm #1343Mark,
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).aspxIf 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.
August 8, 2015 at 6:13 pm #1347That 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 🙂
-
AuthorPosts
The forum ‘Power Pivot’ is closed to new topics and replies.