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.

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.

Table Function

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.

  Subscribe to PowerPivotPro!
X

Subscribe

Narayana Windenberger

Narayana (pronounced “Na-ryan-a”) has a broad analytical background with 8+ years of experience in analyst roles requiring both business acumen and IT know-how. After experiencing first-hand how communication costs can cripple turnaround time in projects, he forayed into self-service BI by learning SQL and deploying SharePoint-based Excel dashboards. Skipping the traditional IT middle man, he became the reporting expert as Business Intelligence manager at FoodChain ID, a leader in food certification. More recently he has dived into the world of DAX, PowerPivot and Power BI and enjoys the “problem-solving thrill” that comes from implementing fully automated dashboards.

Narayana has a high standard of excellence, brings a positive attitude to the task at hand, and is deeply curious to understand how things work. In addition to holding an MBA, he is a Microsoft Certified Solutions Associate in BI Reporting and is a Microsoft Certified Excel Expert. 

This Post Has 20 Comments

  1. This is a great tool! I’m new to Dax and half the time I end up finding answers through trial and error. I really enjoyed the Quiz at the begining. It is nice to test your knowledge in this format.

  2. This report is useful, but that light green on white is really hard to read the small text for example in the M drillthrough. Can you change the areas with smaller text to be black on white similar to the main M function table

  3. This is great! I’m actually really interested in learning how you embedded the Power BI visual in this post? This is actually something we’re planning on investigating soon at our company and would greatly appreciate a link to any resources you can share. Thanks in advance!

    Further, do you know if it is possible to pass parameters to an embedded Power BI visual? For example, we have a website portal that requires a login. Depending on who is logged in, can we pass certain parameters to the embedded Power BI visual and filter the visual accordingly?

    1. Hi Kris,

      We embedded this report using the basic “Publish to Web” feature which is free, requires no authentication and makes the report publicly accessible (https://docs.microsoft.com/en-us/power-bi/service-publish-to-web).

      It is possible to embed Power BI reports in your own authenticated applications, but this requires a Power BI Premium or Power BI Embedded license. In addition, you’ll need someone with programming expertise who can write code to interface with the Power BI API. (https://docs.microsoft.com/en-us/power-bi/developer/embedding). Once you have those resources in place it should be possible to achieve what you describe (filtering visuals based on the logged in user).

  4. Why is FIRSTNONBLANK listed as under the DATETIME group, with the return type “Table”? Shouldn’t it have the Return Type listed as Scalar? Even in the description it says that it returns the first value.

    Let me know if I am missing anything. Thanks!

    1. The group classification comes from the metadata (DMVs) of any Power BI model. See Mat’s original post for more details (https://powerpivotpro.com/2018/03/great-function-project-part-1/). It looks like Microsoft lists that function in the “Time Intelligence” category in their documentation (https://msdn.microsoft.com/en-us/query-bi/dax/date-and-time-functions-dax), but in the DMV, they combine Date functions with Time Intellgence into a single category.

      The more interesting part of your question is why it’s classified as a Table function. It turns out that even though it only returns one value it’s a table and can be used wherever a table function is required. If it were a true scalar function, that wouldn’t be the case. Try writing: [Measure] = CALCULATE(SUM(Table[Column]), FIRSTNONBLANK(…)). Because it’s a table function you can use it as a filter to CALCULATE. Conversely, you couldn’t use
      a function like COUNTROWS() as a filter argument to CALCULATE because it’s a scalar function.

      Nice question!

      1. Thank you for the answer and for the links! Now that I know this, it cleans up a lot of my measures–I was treating it as a scalar.
        So this begs another question–it seems to me that it acts as a scalar at times. For example, I can write a measure [Measure] = FIRSTNONBLANK(‘Calendar'[Year],COUNTROWS(‘Calendar’)). If it were a true table function, shouldn’t this throw an error? You can’t just use FILTER on its own. Does the engine have the ability to recognize a one row, one column table and treat it as a scalar? You can also use it in math equations, for example: [Last Year]=FIRSTNONBLANK(‘Calendar'[Year],COUNTROWS(‘Calendar’))-1

      2. Also, if you use [FirstNonBlankMeasure]=FIRSTNONBLANK(‘Calendar'[Year],COUNTROWS(‘Calendar’)) in DAX Studio, you will return a result, but if you do [LastYearMeasure]=FIRSTNONBLANK(‘Calendar'[Year],COUNTROWS(‘Calendar’))-1 then it throws an error because DAX Studio does not return scalar values.

        Any thoughts on this behavior? I know that there is a better way to get “Last Year” via time intelligence functions, but I am just attempting to illustrate my question.

        Thank you again for this post, and to Matt for the Great Function Project! They are awesome resources.

        1. I’ll answer both questions together. Yes! You’re exactly right, when a table functions returns only one row and one column, the DAX engine converts it to a scalar value if the context it’s in requires it. That’s why when you do FIRSTNONBLANK without the -1, DAX Studio accepts it and returns a table. However when you subtract 1 from the result, it gets converted to scalar to handle the arithmetic and exhibits the behavior you cited.

          Thanks for your positive feedback!

  5. Great! Thanks.

    Do you know if there is any list that shows the DAX commands that use Syntax Sugar and show what the real DAX Syntax is?

      1. +1 to that idea. I find I can only really understand syntax sugar DAX once I see the ‘real’ dax. Realizing the existence of syntax sugar was one of the first key learning moments of DAX for me. Once I saw underlying code things began to click.

  6. Hi Narayana, in the ‘Other Types of Functions’ section of your article you mention functions that return Boolean values and modifier functions as being in a category of their own. A boolean value is a scalar value and the two functions you use as examples of modifier functions are both table returning functions so why do you categorize these as special types different from scalar- and table functions?

    1. You have rightly pointed out a couple of gray areas where I made judgment calls to simplify the organization of functions for the report.

      As you said boolean functions are technically scalar functions, but I made an editorial decision to separate them out because they are so unique in that they only return true or false. Regarding modifier functions, if you look at the four modifier functions in the report (CROSSFILTER, USERELATIONSHIP, IGNORE, KEEPFILTERS) you’ll see that KEEPFILTERS is the only one that could return a table. However, in most cases people would use it as a CALCULATE modifier, so I included it in the Modifier section.

      1. Well in the hurry I actually misread CROSSFILTER for CROSSJOIN and together with your mentioning of SUMMARIZECOLUMNS in the paragraph I became confused. It makes much more sense now.

        KEEPFILTERS definitely is a modifier perhaps even *the* modifier. As a matter of fact I would go as far as saying that KEEPFILTERS doesn’t return a table but just happens to take a table expression as an argument and it is this table expression that returns a table. KEEPFILTERS just modifies the usual replace behavior. I guess this is what you mean when you write ‘KEEPFILTERS is the only one that *could* return a table’

        Just one question: The reason why you mention SUMMARIZECOLUMNS is this that the traces (using DAX studio) from power bi usually contain KEEPFILTERS together with SUMMARIZECOLUMNS?

        Best regards Jes.

      2. When I mentioned SUMMARIZECOLUMNS I was referring to the IGNORE function. I’ve never used it myself (and I’ve only seen it in DAX Studio traces you’ve mentioned) but based on its description it doesn’t do much except for modify which rows SUMMARIZECOLUMNS returns.

        1. OK. I think IGNORE is mostly used by client tool developers like the Power BI team to beat/extend the blank row removal and introduce helper columns that can be used in populating the visuals etc. I haven’t used it myself either. Thank you for taking the time to answer my questions.

Leave a Comment or Question