Programming with DAX

Cite as: AIP Conference Proceedings 2505, 060011 (2022); [Link]

Published Online: 06 September 2022

Veselina Naneva and Kremena Stefanova

AIP Conference Proceedings 2505, 060011 (2022); [Link] 2505, 060011

© 2022 Author(s).
 Programming with DAX
Veselina Naneva a) and Kremena Stefanova b)

Plovdiv University “Paisii Hilendarski”, Plovdiv, Bulgaria

a)
Corresponding author: vnaneva@[Link]
b)
kstɟfanova@[Link]

Abstract. Data processing is a complex procedure and contains methodologies, tools and logical sequences for extraction,
transformation, loading and appropriate data visualization. Nowadays there are variety of BI related tools working both on
a desktop level software and as a cloud service. One of the most recognizable software solutions for complete data
transformation combined with a large number of possibilities for its visualization is Power BI. In such kind of software, it
is required to combine M formula language and Data Analytics Expression (DAX) in order to have optimized interactive
report based on new data-type columns, calculations and visual components. The focus of this paper is to make an overview
of the DAX elements and to show what the impact on the data is when it is used a complex calculation. In addition, it will
be suggested an approach how the created syntax of the DAX calculations to be optimized.

INTRODUCTION
Nowadays every human activity generates different type of information. Additionally, this data needs to be stored
and processed in order to improve a specific service or product. Businesses are working simultaneously with all kind
of data, i.e. past, current, or forecast. The question that should be raised here is how the data processing functions and
logically prepared structures could be applied without respect to the time period, and what kind of tools are coping with
business related problems.
In connection with the considered issue, we can look back to Business Intelligence (BI), since it is focused on
answering plenty of business questions [2, 3]. This sphere is closely related to three main data processing levels –
extracting, transforming and loading of data. Each of these segments stores itself a variety of logical steps in order to
provide valuable knowledge. To contribute a dynamic data manipulation we can use a well-structured process flow for
ETL architecture in cloud-based services. Although the cloud computing is still an expensive service, every business
can decide what kind of access level to have and how to split the workflow process between local and cloud resources.
Without considering, as it is mentioned in the paper [1], the most commonly heard term perhaps is Software as a
Service (SaaS), in which the application runs on the cloud, eliminating the need to install and run the application on the
client computer. From the point of view of the ETL it is not enough to rely only on SaaS, but it can be blended with in
addition with Platforms as a Service (PaaS) such as Microsoft’s Azure Services Platform. To be more precise, in this
paper we will focus on one possible combination of BI related tools, which completely satisfy this requirement to be
able to process data from different sources and the end product to be a dynamically interactive report.
The first step will be the corresponding technology, which we can use to store and manipulate information. Azure
service includes plenty of features related to BI data processing and it is suitable for data warehousing administration.
It will be reasonable also to have Microsoft alternative concerning data visualization. Such kind of software product
reflects on actual data presentation by the help of integrated data analysis expression language, data model
customization and query environment. For instance, Power BI contains three main fields – visualization segment, data
overview and data model.
As it could be concluded from the Fig. 1, Power BI is taking place in every step of the data processing flow. Taking
into account the collections of the data, we could specify several possibilities about how information to be integrated
into Power BI, such as Direct Azure connection (or other cloud-based services), records stored in SQL server or

Applications of Mathematics in Engineering and Economics (AMEE’21)

AIP Conf. Proc. 2505, 060011-1–060011-7; [Link]
Published by AIP Publishing. 978-0-7354-4396-9/$30.00

060011-1
extracted data from a local database. Power BI as PaaS could provide three coherent ways data to be visualized – by
desktop version of the software and then exported to an appropriate user format, by browser and by mobile service –
dynamically presentation of the interactive dashboard. Tools related to Power BI data processing that need to be
considered in details are M Language and DAX expression [4–8]. Both of them can work independently and
simultaneously in order not only to transform existing data but also to create new tables in the data model. Of utmost
importance is to clarify their specification so that we can improve the end product performance.

FIGURE 1. Process Flow of Power BI

M FORMULA LANGUAGE
Although M Formula language is not the focus of this paper, since it is one of the means for data processing on BI
level it is important to be briefly described. This language is the base of Power Query Editor in Power BI and it is
intended for filtering and data combination on query level. The expression, which is yielded in M language, represents
a specific evaluation and it could have self-reference in order to present the required result. Additionally, as a
specification, in M we can use functions, which are in nature a mapping set of input values to a single output value. In
some cases, this technology could be considered as a step of data transformation due the fact that we could combine
several fields from a database in one column. On the other hand, this will have a specific impact on a table in the data
model in a certain dashboard. As examples, we can point out joining or appending tables, logic for indexing or changing
fields’ type.
As we have already mention, Power Query is the environment where we can transform structured data into new
formed collection of information. Fig. 2 represents an example of application of M language in Power Query. We have
created an advance looping illustrative instance in order to focus on syntax specification. The result of the used code is
an array of elements, directly extracted from an open-source API. As it can be seen, a let-expression is used in order to
encapsulate a value from a certain calculation into a variable. To run the result, we need to take the last row in the
variable-list of the declared M-function.

FIGURE 2. Advance Looping with M language

060011-2
 DATA ANALYSIS EXPRESSION LANGUAGE
DAX Language is defined as a formula expression language used in Analysis Services, Power BI, and Power Pivot
in Excel. The combination of different functions, operators and values gives us the opportunity to create new
calculations or to process an existing data column.
Figure 3 illustrates what is the specific structure for each DAX expression when we define a new calculated column
or a measure. Each elements contains the following sections:
x A: Name of the column/measure. It should be unique name for the measure and not unique for the calculated
column, i.e. we could have equal column name but in different data tables in the Power BI model.
x B: An operator for value assignment. After the sign for equality could be set also a variable if needed.
x C: Defining DAX function.
x D: The entire expression. This section could include several arguments if it is required from the certain
function.
x E: Name of the table in the Data model.
x F: Name of the column that is required for the calculation.

FIGURE 3. Basic DAX expression

Needless to say, DAX editor, which is integrated in Power BI, provides possibility to see the syntax of the required
function. In order to be more detailed in illustrating programming with DAX, we need to specify the main calculation
categories and their related functions.
An example of particular purpose category is the set of functions, related to Finance. By the help of DAX, they are
used to perform financial calculations, such as net present value, accrued interest, cumulative principal paid and other.
They are similar to those in Microsoft Excel but the difference in BI software is that each formula is applicable not only
by cells, but also simultaneously by column and row. Example of such kind of financial problem is the necessity to be
calculated Depreciation of an asset. To calculate the straight-line depreciation for one period, i.e. depreciation amount
is the same over each period of the asset’s life, then we can use the predefined function in DAX – SLN().
SLN(<cost>,<salvage>,<life>)
CODE 1. SLN function syntax
In the code above is presented syntax of calculation of straight-line deprecation. The logic behind the SLC defines
the cost of the specific product minus value at the end of the depreciation and all this divided by the life of the assets
(number of periods over which the asset is depreciated). Therefore, by the help of a specific formula we could avoid
using mathematical estimation of deprecation.
Another important group of functions is information ones and they are defined expressions, which are formed to
work with cells or with columns in order to provide a solution if the required information is matching any of existing
values. In this category we could note one of the most used function as for taking the value from the user selection –
SELECTEDVALUE(value). Additionally, we could give as an example a statement, closely related to Row-Level
security. This is the function USERPRINCIPALNAME (). As it could be seen, this is no parameter expression and it
returns the user principal name in the local logging “session”. This is required in order to verify if the specific user
could or could not have the rights to see the ready interactive report with the corresponding visualizations.
Mathematics functions are expressions which are pretty similar to those used in Excel but of utmost importance is
to emphasize on the fact that they do not required yielding of a specific cell, but working with every row from the
indicated table. Interesting example here is the simple aggregation SUM. It could summarize a value based on a filter
context and could be used both in calculated columns and in measures. It accepts column name as only parameter. The
problem behind the use of simple aggregation like this is derived from the impossibility to include complex expression

060011-3
inside of the function. When we need to calculate for example the profit of the products per specific category, we need
to summarize first the sales of all products and to subtract their cost. This cannot be done in one calculation and often
this causes slowdown in the performance of the end visualization.
SUMX(<tablename>,<expression>)
CODE 2. General syntax of SUMX function
In Code 2 is define an alternative function of what we previously described. As it could be concluded from the
required parameters in SUMX, opposed to SUM, we could yield as arguments a table name and an expression for fields
no matter if the table is the same with this inside of which it will be the new calculation.
PROFIT=SUMX(
ExampleTable,
ExampleTable[Sales]ŞExampleTable[Cost]
)
CODE 3. Example syntax of SUMX function
To create an overview of the logic hidden in SUMX, we need to define what specify temporary memory in Power
BI calculation. When we create a new calculated column, as it is described several paragraphs above, it is placed in the
actual storage of the report and can be used as a key to relate two or more tables. Temporary memory in this case could
be determined as tool for virtual storing the result of expression calculation for one row at a time. At the end of parsing
the table and calculating all values for every single row, it will summarize them all together, as shown in Code 3, then
will release the temporary memory, and finally will visualize the result. In addition, there are other functions combined
with the same logic, named the Iterator functions.
In turn, the relationship category defines expressions, which are used for managing and utilizing relationships
between tables. Most of the time we have well-prepared data and the tables in the data models are stored with appropriate
relationship key. In fact there are cases when two tables need to be mapped to one field in the third one and based on
the requirement to have only one active relationship, we need to use an additional function. The expression
USERELATIONSHIP requires two parameter which is actually the key field for two tables no matter if they have active
or passive relationship between each other.
CALCULATE(
SUM(ExampleTable[Sales]),
USERELATIONSHIP(ExampleTable[OrderDate],DateTable[Date])
)
CODE 4. Example syntax of CALCULATE function
Certainly, as it could be seen in the example of Code 4, USERELATIONSHIP() could be included as a filtering
expression in every other function. It is a simple way to specify based on what to be applied the aggregation. This
relationship function could be used both on column or on measure level.
DAX includes functions, which work with different date/time type fields in order to process data and to create new
calculations. They are combined in the category of Time related functions. By definition this type of expressions
manipulate the information using days, months, quarters, and years, and build comparison calculations over those
periods. Of utmost importance is to emphasize that most of the functions automatically compared time difference by
current moment, such as SAMEPERIODLASTYEAR(), NEXTYEAR(), ENDOFYEAR(), etc.
Filter functions are some of the most complex and various expressions in DAX language. This category stores
possibilities for dynamic calculations by manipulating data context, using single function or combining several in one.
Additionally, every function can be implemented in calculated columns and in measures. For instance, we could
consider several important expressions here. The function LOOKUPVALUE() returns result for the row that satisfy all
specified criteria by search conditions and can apply one or more search rules. It is appropriate to be used as yelling
method for a certain column when there is no relationship between two tables and the function RELATED() is not
applicable.
Furthermore, in this classification refers also the CALCULATE() function which conditionally requires two
parameters – expression and filter for it. Filters can be Boolean expressions, table filtration or overall modification
functions. Each type of filter should be followed by specific rules, i.e. when we define a condition related to different

060011-4
table, the second parameter should start with FILTER() function or if we use Boolean filter expression we cannot return
a table, including aggregation functions.
DIVIDE(
SUM(ExampleTable[Sales]),
CALCULATE(
SUM(ExampleTable[Sales]),
REMOVEFILTERS(ExampleTable2[Type])
)
)
CODE 5. Example syntax of DIVIDE function
We can consider from the example in Code 5 that the Filter functions can be used in one measure combined in order
to better process the calculation. In this case, we need to have a percentage result for sales amount, spit over different
types. We should specify the total sales amount and in CALCULATED function and remove the filtration that is
possible to be applied outside of the calculation. To be sure that each type, displayed in the visual is going to have its
own percentage, the calculation should be entered not in the calculated column, but in the measure. The question of
how could we improve such types of expression will be considered below.
Table manipulation functions are responsible for manipulating of an existing table or for creating new ones, based
on rules, which are also complex expressions. They except different numbers or types of parameters and their
application changes sometimes the structure of the data model. An appropriate example here is SUMMARIZE()
function which returns a summary table for the requested grouped information.
SUMMARIZE(ExampleTable,
DateTable[Year],
ExampleTable1[Name],
"KG",SUM(ExampleTable[KG]),
"Price",SUM(ExampleTable[Price])
)
CODE 6. Example syntax of SUMMARIZE function
From Code 6 it could be considered what kind of arguments we have in the function. We could specify from which
existing table to be taken the aggregated data. Also, it could contains fields from different sources and additionally, an
expression, which need to be stored in user-called new columns. The new table could have a key field to which other
tables from the data model to rely on. Of course, we could conclude that every kind of complex expression, such as
combination of previous DAX types could be applicable to last several parameters in the suggested example.
Of utmost importance is variable to be considered in forming complex functions. In DAX syntax, VAR is a keyword
for storing a result of an expression as a named variable. That element could be used not only as an argument in most
of the DAX functions, but also both in calculated columns and measures. Additionally, once the calculation of a specific
variable has been made, the result does not change, even if the same variable is referenced in other measure or calculated
column. As a requirement from DAX language, when we define a variable and assign a value to it, the calculation
should end with an operator RETURN in order to distinguish where the place of actual estimation is.

SIMPLIFYING AND OPTIMIZING THE SYNTAX.

APPLICATION OF DAX LANGUAGE
To specify the actual DAX syntax and to propose methods for optimization, we will illustrate an example, based on
Microsoft Power BI samples. Let us take a database, which includes tables for sales marketing and there are fields
related to profit, sales, cost of the products, and HR information, such as personal user data, etc.

060011-5
 Figure 4 presents year to date total units percentage by the exact calculation in a new measure. We use the predefined
function TOTALYTD that requires two main and two optional parameters. The first expression returns a scalar value,
which in the current case is the summation of all units. As a second argument, we need to specify certain date range.
Currently, it is used calculation for taking data column inside of the second argument. Although it is an optional
parameter, in this case we need to add function SAMEPERIODLASTYEAR() which automatically gets the same date
but in last year. As year end date parameter, we use different calculation. If we set the measure, shown on Fig. 4, as
field in a matrix table in combination with category hierarchy, we can conclude that the values are not correct. One
solution here can be every argument of the embedded function to be split into separate measures. Thus, we can
implement the same logic but in a much simple way so that we could receive a result with an appropriate calculation.

FIGURE 4. Example syntax of a measure

We could conclude from Fig. 5 that when Power BI measure has smaller data set to process and store in the memory,
i.e. as it is the main definition of its application, it returns the expected results. In the current example the only argument
is the division function which contains already calculated results for Total unis for the same period last year and total
unit through the time.

FIGURE 5. Example syntax of optimized measure

If we take into account the overall performance, one of the helpful tools will be DAX Studio. It gives the opportunity
to evaluate every calculation. With the granulated approach, we tested the impact on the end process timing and we
found out that not only the result has been precised, but also the visualization time has a significant improvement.
The statement above is proved also by the information shown in Fig. 6. As it can be seen, the processing time for
the measure is 13 ms, while the calculated column used in Fig. 4 takes the summary time of 30 ms. For a simple
dashboard it will not have much impact on the overall performance, but practically BI systems such as Power BI have
wider application for data processing and data visualization.

060011-6
 FIGURE 6. Performance analysis in DAX Studio

CONCLUSION
Data processing is closely related to variety of techniques, tools and methods, by which we could extract, transform,
load and visualize required information. Talking about business intelligence, there are different services or software
products, but solutions such as Microsoft Azure cloud provider and Power BI makes significant impact on creating an
interactive dashboard. Additionally, we need to focus not only on the specific tool for constructing reports and
visualizations, but also on the importance of the language behind the data processing. By the help of DAX and M
languages, we could manipulate existing columns into the database or add brand new tables into the model. The
complexity of the DAX language is one of the prerequisites for low report performance. As we demonstrate in the
examples above, the syntax and its complexity affect not only the performance, but also the correctness of the results.

ACKNOWLEDGMENT
In this paper, V. Naneva is partially supported by project MU21-FMI-007 and K. Stefanova is partially supported
by project MU21-FMI-009 of the Scientific Research Fund of Plovdiv University “Paisii Hilendarski”, Bulgariɚ.

REFERENCES
1. R. Moser, S. Rengarajan, and G. Narayanamurthy, Decision Intelligence: Creating a Fit between Intelligence
Requirements and Intelligence Processing Capacities, IIM Kozhikode Society & Management Review, June 2021,
doi:10.1177/22779752211017386.
2. E. Ahmed, Utilization of Business Intelligence Tools among Business Intelligence Users, International Journal
for Innovation Education and Research, 2021, Vol. 9, p. 237–253, ISSN: 2411-2933, doi:
10.31686/ijier.vol9.iss6.3172.
3. M. Muddasir and R. Dayanand, Towards Comparative Analysis of Resumption Techniques in ETL, Indonesian
Journal of Information Systems, 2021, Vol. 3, p. 82–93, ISSN: 2623-2308, doi: 10.24002/ijis.v3i2.3776.
4. Quick tour of the Power Query M formula language, [Link]
tour-of-the-power-query-m-formula-language
5. Differences between the M Language and DAX in Power BI, [Link]
the-m-language-and-dax-in-power-bi/
6. Basics of M: Power Query Formula Language, [Link]
7. Complex Power Bi Reports Using Dax Functions, [Link]
using-dax-functions/
8. Use variables to improve your DAX formulas, [Link]
variables

060011-7