DAX HANDBOOK
4.3 Mixed functions

If you wish to follow along, you can find PBIX/Excel files at the bottom of the article. 

Authors

Krešimir Ledinski

Krešimir Ledinski

Microsoft certified expert in the field of Business Intelligence. His biggest passions are DAX, M, and data modeling.

Kristian Radoš

Kristian Radoš

Experienced data analyst. Advanced in SQL, PowerApps and M language.

Explanation

Mixed functions are the ones that can form group-by of multiple columns and define different data granularities while having the ability to invoke row context over the newly formed tables. The most important one is SUMMARIZE.

SUMMARIZE is mostly used as a replacement for VALUES function when we need to create crossjoin of multiple columns from the model while accepting filter context. VALUES function can only accept a single column or the whole table as an argument, but if we need to form a table from multiple columns then SUMMARIZE is the preferred way to go.

SUMMARIZE syntax:

SUMMARIZE (Table, [GroupByColumn1, GroupByColumn2, …], [[ColName1], expression, [ColName2], expression, …])

  • The first argument is the table from which we wish to group columns.
  • The second type of argument is a list of columns we wish to include in a grouped virtual table.
  • The third type of argument is defining the new column we want to add to the virtual table with the expression that will be evaluated in a row context of the summarized table. The 3rd argument is rarely used. If we need to add an additional virtual column to the SUMMARIZE function, we should use ADDCOLUMNS function instead because, in most cases, the latter produces a faster query plan.
VALUES_Category = COUNTROWS(VALUES(Sales[CategoryName]))

VALUES can only accept a single column or a whole table as an argument, which limits its application. It accepts filter context.

ALL_Category/Region = COUNTROWS(ALL(Sales[CategoryName],Sales[SalesTerritoryRegion]))

ALL function can be used with multiple columns, but its side effect is that it ignores filter context

SUMMARIZE_Category/Region = COUNTROWS(SUMMARIZE(Sales,Sales[CategoryName],Sales[SalesTerritoryRegion]))

SUMMARIZE is the best replacement for the VALUES function when we need to cross-join multiple columns. It does that while accepting filter context.

SUMMARIZE notes:
Like with the ADDCOLUMNS function, its usefulness becomes clear after learning the power of the CALCULATE function.


SUMMARIZE can access any table in a data model connected to the table defined in the first argument, as long as that table has a one to many relationship with the main table. The data model concept will be explained in the following chapter.

Materials

We wish to create the best possible content!

If you are a novice looking for a better explanation of any element of the topic, feel free to comment on the part you didn't quite understand!

If you are an expert in DAX and believe certain topic lacks important internals, your comments are more than welcomed!

COMMENTS

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments

OUR SERVICES

Prefer live training or consultations?

Table of Content

Table of Content

GET LATEST BI NEWS TO YOUR INBOX

Receive the latest updates on all business analyst news across all platforms.

By subscribing you are agreeing to our Privacy Policy.

Related blog posts

DAX HANDBOOK
7. VARIABLES

Variables are used in almost every measure you will create. The reason we introduce them last is that they use all other parts of DAX code to produce faster, more powerful and maintainable code. Variables are like containers of a part of the DAX code which can be used throughout...

Read more

DAX HANDBOOK
6.8 ALLSELECTED

Explanation ALLSELECTED is one of the most complex functions in DAX. When used improperly it can lead to unexpected results. As a rule of thumb, you should not use it in iterative functions. It is preferable to use it only as a CALCULATE filter remover, not as a table function....

Read more

DAX HANDBOOK
6.7 Lineage

What is Lineage? Lineage is a part of DAX mechanics that enables us to use tables as filter arguments for the CALCULATE function. It’s also used in row2filter context transition and other aspects of the data model that involve filter propagation throughout the model. We can state that lineage is...

Read more

DAX HANDBOOK
6.6 Crossfilter

Crossfilter is a feature of DAX when it filters the underlying dataset even though there aren’t any visual filters present. Introduction In this example, we will explain a very important feature of CALCULATE filter arguments. We will also explain why you should always prefer the combination of ALL/REMOVEFILTER + VALUES...

Read more
0
Would love your thoughts, please comment.x
()
x
Scroll to Top