Power BI DAX Back to Basics: Scalar Vs. Tabular Functions

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

DAX is the analytical language in Power BI. Learning this language, however, comes with some difficulties. Part of that challenge is the way that functions work in DAX. Learning the output of functions is an important element of learning how and where you can use them. In this article, I’ll explain two types of outputs for DAX functions, and ways that you can use them.

Scalar Functions

Scalar function in a function that returns one single value. This value can be of any data type; Date, Numeric, Text, etc. But it is always one single value. One of the most basic and simple functions in this category is SUM.

Consider the measure below:

This calculation will return one single value. Depends on where you use it in the report actually, but here you can see that it returns the total sales:

Measure Sales return one single value

If you use it in a table, however, you will see multiple results. But in fact, that is one result, per the filter combination applied in the table.

SUM function returns one single value as a result, based on the filter applied.

We have many Scalar functions in DAX, here are a few examples:

  • SUM/Average/Min/Max
  • SUMX/MinX/MaxX/AverageX/CountX
  • LastDate/FirstDate
  • Calculate
  • Related

You can use these functions directly in a Measure or Calculated Column. If you don’t know the difference between these two, read my article here.

The result of a Measure or Calculated Column should be one single value.

If these functions are used in a Measure, then you will see the result of the measure in the visual. If they are used in a calculated column, you will see the result of those in every row of the table;

The calculation above returns one single value per each row in the Customer table.

Tabular Functions

Some functions return a table as the output, not a single value, a whole table. The table can have multiple columns or just a single column depends on the function used. But it would be a table structure with multiple values in it.

One of the most simple tabular functions is ALL. All is a function that returns the entire table without any filters applied (if just the table passed as the input with no changes).

The expression above, as a Table, will return the below result;

ALL returns a table as the result

There are many tabular functions in DAX, here are a few;

You can use these functions mainly in a calculated table. To learn more about the calculated table read my article here.

Tabular functions can be used directly in a Calculated Table.

Miss-Use of the Functions

So far, everything seems simple. The first problem appears when you want to use a function in a place that you shouldn’t. For example, if you use a scalar function to build a calculated table! or use a tabular function to return the main result of a DAX measure!

Scalar Function to Create a Calculated Table

This won’t work. a function that returns one single value, cannot build a table.

Scalar function cannot be the main output of the calculated table.

If you do this action, you will get the error saying: “the expression specified in the query is not a valid table expression.”

That is because the Calculated table expects a table expression, which only comes from tabular functions.

Tabular Functions to Return the output of a Measure or Calculated Column

another miss-use of the functions is when you use a tabular function directly in a measure or calculated column to return the output.

Tabular function cannot be used to generate the main output of a measure or calculated column.

If you do this action, you will get the error “The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.”

This is because the function returns a table, and the measure expects a single value.

What can You DO?

DAX is a language of nested functions. One function can be input of another function. So, in fact, you can use scalar functions to build a calculated table, or you can use tabular function to create a measure, you just need to know how. Here is the guidance.

Using Tabular Functions in Measures

although you cannot use the tabular function result as the main result of the measure, you can use that as an intermediate result used inside a scalar function.

The expression below, is calculating the sales of the same period last year;

The expression below is written as a measure, yet, you can see that I have used the SamePeriodLastYear in it which is a tabular function.

Tabular function can be used inside a scalar function as a table expression parameter.

As you can see in the above screenshot, the result of the SamePeriodLastYear (which is a tabular function) is used as the 2nd parameter of the Calculate (which is scalar function). And because the main output of the measure is coming from the function the is the direct result of the measure, it works perfectly fine.

Using Scalar Functions in Calculated Table

You can use the same approach and cascade scalar functions inside a tabular function.

The expression above uses the CountX, which is scalar function, inside the GroupBy as a parameter. And the GroupBy is a tabular function. If you look more in details, you will also see that the CurrentGroup function is another tabular function nested inside the CountX.

Scalar function used as a parameter inside a tabular function

The key understanding from the examples above is that functions should be used in the places that their output type is expected.

How Should I Know Where to Use Each Function?

This is a very fair question. Always look at the definition of the function, or use the MSDN Docs. For example, here is the definition of the SUMX Function;

Sometimes input parameters and output type is understandable from the function definition in the Editor.

The Docs would also guide you to the same thing;

https://docs.microsoft.com/en-us/dax/sumx-function-dax

Not all functions have a very good definition guide, but most of them have it.

Restrictions

There are some functions that can be used only in a specific context. For example; The Calculate returns a scalar value, but it cannot be used inside a GroupBy function. That is the limitation of the GroupBy function.

calculate function is not allowed as the expression for the GroupBy function.

You will find restrictions like these in some functions, however, mostly you will find the docs that explain the restriction.

Check out the Docs to find out if there is any restrictions.

Exceptions

There are functions that are neither tabular nor scalar. They don’t return an output. For example, the CrossFilter function is changing the behavior of a relationship, and can be used only inside a Calculate Function.

Summary

Understanding DAX requires you to change your mindset from programming languages or even expression languages. the difficulty is mainly understanding the filter context. However, another part is understanding the output type of each function. You should know how and where to use tabular or scalar functions in DAX. In this article, you learned that you can nest these functions inside each other in the right way, but always keep an eye on restrictions and exceptions.

Video

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

Leave a Reply