Scalar and table functions

The majority of DAX functions return a single value based on an aggregation or a logical evaluation of a table or column. For example, the COUNTROWS() and DISTINCTCOUNT() functions return individual numeric values based on a single table and a single column input parameter, respectively. The DAX functions which return individual values as their output, including information functions, such as ISBLANK() and LOOKUPVALUE(), are referred to as scalar functions. For relatively simple datasets and at early stages in projects, most DAX measures will reference a single scalar function with no other modifications, such as with CALCULATE(). 

In addition to scalar functions, many DAX functions return a table as the output value. The tables returned by these functions, such as FILTER() and ALL(), are used as input parameters to other DAX measure expressions to impact the filter context under which the measure is executed via the CALCULATE() function. The DAX language has been extended to support many powerful table functions, such as TOPN(), INTERSECT(), and UNION(), thus providing further support for authoring DAX measures.

In addition to serving as table input parameters to DAX measures, the results of DAX table functions can be returned and exposed to client reporting tools. The most common example of this is in developing a paginated reporting services report either with SQL Server Reporting Services (SSRS) or the Power BI Report Server based on an Analysis Services Tabular model. Additionally, DAX table functions can return a summarized or filtered table within a Power BI dataset based on the other tables in the dataset. 

As models grow in complexity and as model authors become more familiar with DAX, new measures increasingly leverage a combination of scalar functions (or existing measures based on scalar functions) and table functions. Per the DAX Variables section later in this chapter, both scalar and table values (based on scalar and table functions, respectively) can be stored as variables to further support abstraction and readability.