In this Pragmatic Works training video, Jeremiah Hersey wraps up his six-part series on virtual tables in DAX by focusing on the SUMMARIZECOLUMNS function. He explains that this function is designed as an all-in-one solution for creating grouped table queries, adding calculated columns, and applying filters. While it is a powerful tool, Jeremiah also emphasizes that it comes with limitations, especially when used inside measures.
Jeremiah introduces SUMMARIZECOLUMNS as a function that combines capabilities found in several other DAX functions. It can group data similarly to SUMMARIZE, add expressions like ADDCOLUMNS, and apply filters in a way similar to CALCULATETABLE. Because of this, it can be a very efficient option when building table queries in Power BI.
One of the main takeaways from Jeremiah’s explanation is that SUMMARIZECOLUMNS is built to simplify query creation by combining several steps into one function. Instead of chaining together multiple functions, users can often accomplish the same result within a single statement.
Jeremiah highlights the three major capabilities of the function:
In the demo, he creates a new table in Power BI Desktop and groups data by Product Class and Year. He then adds a new column called Amount, which uses an existing Total Sales measure. This demonstrates how the function can quickly produce a summarized table that is both grouped and enhanced with additional calculations.
Jeremiah points out an important default behavior of SUMMARIZECOLUMNS: it automatically removes rows where the result of the added expression is blank. This is helpful in many cases because it keeps the output table clean and focused only on rows with meaningful values.
However, there may be times when blank values still need to appear in the result set. To handle that, Jeremiah introduces the IGNORE function. By wrapping the expression with IGNORE, Power BI is instructed to return rows even when the calculated value is blank.
This gives report developers more flexibility depending on how much detail they want in the output. In short:
Another major feature Jeremiah demonstrates is filtering. He explains that SUMMARIZECOLUMNS can include filter logic directly in the function, making it even more powerful for query scenarios.
In the example, he applies a filter to the Customer table using the FILTER function along with ALL. The condition checks for customers whose marital status is equal to Single. Once this filter is applied, the resulting sales values change to reflect only that filtered customer group.
He then changes the filter from Single to Married to show how the summarized results update accordingly. This makes it clear that the function is not just grouping static data. It can also return dynamic results based on the filter conditions added inside the query.
Although the video focuses on the core behavior of SUMMARIZECOLUMNS, Jeremiah briefly mentions that the function has even more advanced capabilities. These include support for subtotal and rollup logic through functions such as:
He does not go deep into those features in this lesson, but he notes that they can be used to generate subtotal-style outputs when needed. This makes SUMMARIZECOLUMNS even more valuable for advanced reporting and query design.
Even though SUMMARIZECOLUMNS is powerful, Jeremiah stresses that it is not recommended for every scenario. Specifically, it is designed for running queries and is not generally recommended inside measures. The reason is tied to context transition.
Jeremiah explains that if the external filter context has performed context transition, the function may not behave properly. In those cases, using SUMMARIZECOLUMNS inside a measure can lead to incorrect or unsupported results.
For measure-based scenarios, he recommends using a combination of SUMMARIZE and ADDCOLUMNS instead. This gives developers more reliable behavior when context transition is involved.
Jeremiah Hersey closes the video by reminding viewers that SUMMARIZECOLUMNS is an extremely useful DAX function for query building in Power BI. It offers grouping, calculated columns, filtering, and even advanced subtotal capabilities in one place. At the same time, users need to understand its default handling of blanks and its limitations around context transition.
For Power BI professionals looking to build stronger DAX skills, this lesson from Pragmatic Works provides a practical and clear introduction to when and how SUMMARIZECOLUMNS should be used. It serves as a strong conclusion to Jeremiah’s virtual tables series and gives learners a solid foundation for more advanced DAX work ahead.
Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Dax and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.