There are a lot of resources about how to use each function. However, you always need to start with learning how the expression language works itself. You need to learn how to reference columns and tables. what operators can be used, and what are the very simple basics of writing a DAX expression. In this article and video, I will explain that.
Video
What is DAX?
DAX is an acronym for Data Analysis Expression language. This language is used in Microsoft’s data analysis products such as: Power BI, Excel Power Pivot, SQL Server Analysis Services Tabular Edition, and Azure Analysis Services. The language is a combined version of a bit of T-SQL, Excel formula, and C#.
DAX is an expression language, which means most of it is written after an equal sign (=) as a formula. There are hundreds of functions that can be used for doing different things. However, the expression language itself, has some basics and fundamentals.
DAX as a calculation
DAX is most commonly written as a calculation. The calculation comes in three forms below;
- Calculated Column
- Calculated Table
- Measure
This article will be very long if we want to discuss the difference between these three types of calculations. I have a couple of articles, which I strongly recommend you to read about it;
- Measure vs Calculated Column: The Mysterious Question? Not!
- Scenarios of Using Calculated Tables in Power BI
DAX as a row-level security expression
DAX can be also used in writing logical expressions for row-level security purposes. This means using DAX a calculation that only returns a Boolean value (true or false).
To learn more about row-level security, read my articles about it here.
DAX as a query language
DAX can be used also as a stand-alone query. However, you need to have a specific setup to run that. You need to be connected the model using a tool such as SSMS (SQL Server Management Studio) or DAX Studio. And then write the query and get the results through those tools. Often when we use tools such as Power BI and Excel, the tool itself creates the query behind the scene. Writing DAX as a stand-alone query is not happening very often. As a DAX developer, you will spend most of your time writing DAX calculations, that is why I focus on that part in this article.
DAX expression syntax
When you write DAX as an expression for a calculation. The expression always comes after an equal sign (=), here are a few examples:
- =10
- =”sample text”
- =2*10
- =Sum(TableX[ColumnY])
- =Calculate( Sum(TableX[ColumnY]), TableX[ColumnZ] =”sample text” )
all the examples above shows an expression after the equal sign. The expression can be a literal value such as number or a text. If it is text, then it is wrapped inside double quotes (“). You can use operators such as * or / in the expression too. and You can use Functions (Such as Sum and Calculate in the above samples), and you can reference to other columns, tables, and measures.
Referencing objects
An important part of a DAX expression is when you reference other objects. For example, you can create a column, which can be exactly equal to another column;
To reference a column, you need the column name inside [ and ]. The table name can appear before that. If you are using that column inside the same table, you can skip the table name, and just have it as below too;
Column = [FullName]
The above works as long as the Column I have created exists in the same table that the FullName column exists. So as a best practice, it is advised to have the table name before the column name. The table name comes just normally before the column name like below;
Column = DimCustomer[FullName]
If the table name has some special characters (and space is also considered as a special character), then the table names comes inside single quotes (‘) like below;
this is how the expression will be;
Column = 'Dim Employee'[FirstName]
Calculated columns can be referenced exactly similar to normal columns. Calculated tables can be referenced exactly similar to normal tables.
Measures can be referenced with the same structure as columns with [ and ], and the table names in front of them. However, for the measures, it is advised to NOT use the table name, because you may move your measures between tables.
Operators
There are many operators you can use within DAX expressions. Operators are in below categories;
| Operator category | samples | operation |
| Arithmetic | + * / | adds two numbers multiplies two numbers divides |
| Comparison | = <> => | equal not equal greater than or equal |
| Text concatenation | & | concatenate texts |
| Logical | && || IN | AND OR if the value is IN the list |
Here are some samples of DAX expressions with operators;
- =1+10
- =[ColumnX]+[ColumnY]
- =”Reza”&” “&”Rad
- =If( TableX[ColumnY]>=12, “XYZ”, “WYQ”)
- =”Reza” IN {“Reza”,”Leila”}
- = TableX[ColumnY]>=10 && TableX[ColumnZ]<20
Variables
You can define a variable inside a DAX expression, the variable can store a single value or a table, and can be re-used throughout that DAX expression. When using variables in places that an output is needed, a return keyword is also mandatory. Here is an example of using variables;
Column 2 =
var HireYears=DATEDIFF('Dim Employee'[HireDate],TODAY(),YEAR)
return
if(HireYears>20,"hired for 20+ years","hired for less than 20 years")
As you can see the variable HireYears is defined once, and re-used in the IF statement. To learn more about variables, read my article below;
Comments
In every language, it is useful to be able to write some none-executables lines within the code. This will enable the developer to put some comments for future reference. In DAX you can write commentary using double forward slash characters in one line (//)
Or you can use /* and */ to write comment in multiple lines;
Comments are not executable, and will not impact on the performance or result of the DAX expression.
Functions
And finally the heart of DAX expression is filled with usage of functions. In DAX, there are functions for many different operations. A function can be as simple as concatenating two text values. There are functions to deal with date-based calculations, such as calculating the value for the same period last year. Each function gets input parameters and have an output. Functions can be used inside each other. Here are some expressions with functions;
- Sum(TableX[ColumnY])
- SumX ( All (TableX), [ColumnY]+[ColumnX] )
Functions have wide variety, and normally the intellisense (pop up expression help screen when writing DAX) has a good information about what parameters the function need and what is the generated output.
Functions can be different, but one main way to separated them is tabular Vs. scalar functions (although there are functions that are neither tabular nor scalar). read more about it here;
Functions are also categorized based on the work they do. here are some of the categories;
- Date and time functions
- Filter functions
- Information functions
- Parent and child functions
- Time intelligence functions
- table manipulation functions
- logical functions
- text functions
- relationship functions
- …
Writing a DAX expression is mostly common when using functions. The functions are giving your DAX calculation an immense power, and to be a DAX developer, you need to learn how to work with the functions.
Change the font size of the DAX editor in Power BI Desktop
You can use CTRL +/- to change the font size in the DAX editor in the Power BI Desktop.
Color Guide on Power BI DAX Editor
When you write your DAX expression inside Power BI Desktop, you can see a color code for some words. here are what they mean;
- Light green: Variables
- Green: Comment
- Purple: Measures
- Blue: Functions (such as SUM, SUMX, Calculate), and keywords (such as Year, return, var)
- Red: Text values
- Black: table and column names, literals, and operators
the screenshot below is an example with the colors mentioned.
Summary
DAX as an expression language is used to create calculations in Power BI, Excel Power Pivot, and Analysis Services. There are basics on how to reference columns and tables. Some operators can be used in this expression language. You can define variables for re-using part of the expression, and you are able to write comments in the code. The heart of the DAX expression, however, is when you use functions, and that is where most of your time will be spent when learning DAX.
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.