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

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:

Sales = Sum(FactInternetSales[SalesAmount])

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).

Copy of Customer Table = ALL(DimCustomer)

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;

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.

GroupBy - with aggregation = GROUPBY(
    "Row Count",

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;


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


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.


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.


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.


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 12 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, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.

Leave a Reply