Navigating DAX Function Types: A list of DAX Scalar and Table functions

Let’s start off today’s post with a pop quiz:

Once you’ve answered, click here to see the poll results, and then scroll down to see the correct answer.

…

[vcex_spacing size=”100px”]

…

[vcex_spacing size=”100px”]

…

The correct answer is C. DATEADD is a table function which takes a table of dates as input, shifts those dates by a given number of days, and returns the new table as output.

If you got it right, pat yourself on the back. If not, don’t feel bad! When most people are learning DAX, especially if coming from an Excel background, it takes some time to understand what different functions output and when to use them.

Over my years of DAX experience, I’ve frequently wished that Microsoft’s DAX Reference listed the output type for each function, but since it has yet to happen I decided to create this myself. I’m lucky to work with an amazing team of consultants here at P3, and I’ll build upon the great work that Mat Herring has done with his “Great Function Project.”

Let’s start with a review of table vs. scalar functions and when to use them.

If you want to go straight to the reference click here.

What is a Scalar function?

A scalar function returns a single value. Generally, it’s an aggregator like SUM or AVERAGE which takes a column of data, performs an operation on it, and returns a number or text.

You would use a scalar function whenever you want to return a single cell of data, for example when you want to display data in a pivot table, pivot chart, or Power BI visual.

What is a table function?

Think of a table function as something that returns a table of data (imagine an Excel spreadsheet with rows and columns of data). In statistical programming terms, think of the output of a table function as a data frame.

In DAX, a table function is used in a few ways:

1. As input to another DAX function where the function argument requires a table. For example, the FILTER function iterates over a table of data and tests each value for a condition (actually any X function like SUMX or AVERAGEX is an iterator and would require a table as the first argument.)
2. As a calculated table definition in Power BI Desktop
3. As a query written against your data model using DAX Studio or SQL Server Management Studio.

One key limitation of a table function is that its output can’t be used in a visual in your report. If you think about it, each cell on a pivot table can only fit one number, not an entire table of numbers.

Other Types of Functions

In DAX, the majority of functions return either a scalar value or table. However, there are a few that are in a category of their own:

a) BOOLEAN (TRUE/FALSE): these functions test a condition and return true or false based on the condition (i.e., HASONEVALUE). You’d typically used these in combination with IF or SWITCH.

b) MODIFIER: these functions are only used in the context of modifying the dynamics of the CALCULATE function (i.e., CROSSFILTER) or the SUMMARIZECOLUMNS function.

The Function Output Type Reference

Now that you’re familiar with the different output types, here’s what you’ve all been waiting for. In the embedded report below, make sure you click on the “DAX” link and then filter by your desired “Return Type.”

A Couple of Observations

Once you’ve had a chance to review the Return Types for all your favorite functions, I want to point out a couple of key points.

Tables can become scalars: A neat feature of DAX is that any table function that resolves to a single row and column (i.e., one value) is automatically converted to a scalar value if possible. That means that you can use a table function as an input to another function, but do so cautiously to avoid the dreaded “Table of Multiple Values…” error.

IF/SWITCH can’t return tables: Another subtlety that I noted in my research is that IF and SWITCH can only return scalar values and NOT tables. Have a look at the pseudo-code DAX below:

CALCULATE(
   [Measure],
   IF(
      Condition = TRUE
      ,FILTER(Sales, Sales[ExtendedAmount] < 10) --If the condition is true, return this table filter
      ,FILTER(Sales, Sales[ExtendedAmount] > 10) --otherwise use this table filter
   )
)

Sadly, due to the limitation above this formula will fail to resolve. In one client situation, I could have reduced a 100-line measure to 40 lines if I could have generated a conditional filter table similar to the example above.

Re-cap

What we’ve learned today is that functions can return different data types.  These include scalar values, tables of data, or Boolean true/false results. Also, several functions don’t return any value, but instead modify how another function operates. Keeping these differences in mind is critical to writing and debugging DAX code so be sure to bookmark this page and re-visit as often as necessary if you’re ever stuck. Enjoy!

Where It’s At:  The Intersection of Biz, Human, and Tech*

We “give away” business-value-creating and escape-the-box-inspiring content like this article in part to show you that we’re not your average “tools” consulting firm. We’re sharp on the toolset for sure, but also on what makes businesses AND human beings “go.”

In three days’ time imagine what we can do for your bottom line. You should seriously consider finding out 🙂

* – unless, of course, you have two turntables and a microphone.  We hear a lot of things are also located there.