What is DAX?
Data Analysis Expressions (DAX) is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values. Stated more simply, DAX helps you create new information from data already in your model (is a set of tables, linked by relationships).
History of DAX
DAX is the programming language of Microsoft SQL Server Analysis Services (SSAS) and Microsoft Power Pivot for Excel. It was created in 2010, with the first release for Power Pivot for Excel 2010. Over time, DAX gained popularity in the Excel community, which uses DAX to create Power Pivot data models, Excel, and in the Business Intelligence (BI) community, which uses DAX to build models with SSAS. It is extremely popular today as it is now the language of choice for major BI & visualization tools (e.g. Power BI, Excel) and is supported by Tabular SSAS as well.
Why is DAX so important?
It’s quite easy to create a report in Excel & Power BI Desktop applications after importing some raw data into these. You can even create reports and dashboard that show valuable insights and do analysis without using any DAX formulas at all. But if you are really looking for some complex business insights or want to do analysis to find the growth percentage across product categories and for different date ranges? Or, you need to calculate the business growth year-over-year compared to market trends? DAX will provide all the capabilities to get the most out of your data as well as it helps you in solving real business problems that affect your bottom line.
DAX data types
DAX can perform several computations with different types of data. There are mainly 8 data types in DAX. Please see the below list:
| Sr.No | Data Type | Description |
|---|---|---|
| 1 | Binary | The Binary data type is used in the data model to store images or other non-structured types of information. It is mainly used by Power View and it is not available in Power BI as well as not accessible in DAX. |
| 2 | Boolean | The Boolean data type is used to express logical conditions. It has always True/False value. |
| 3 | Currency | The Currency data type, also known as Fixed Decimal Number in Power BI, stores a fixed decimal number. It can represent four decimal points and it is internally stored as a 64-bit integer value divided by 10,000. Currency data type allows values between 922,337,203,685,477.5808 to 922,337,203,685,477.5807 with four decimal digits of fixed precision. |
| 4 | DateTime | DAX stores dates in a DateTime data type. This format uses a floating-point number internally, wherein the integer refers to the number of days since December 30, 1899, and the decimal part shows the fraction of the day. Hours, minutes, and seconds are converted to decimal faction of a day. |
| 5 | Decimal | A decimal number is always stored as double-precision floating point value. |
| 6 | Integer | Integer data type can store a 64-bit value. |
| 7 | String | In DAX, every string is stored as Unicode string, where each character is stored in 16 bits. By default, the comparison between strings is case-insensitive. |
| 8 | Variant | The Variant data type is used for expressions that might return different data types, depending on the conditions. For example, if you want to return integer value on certain calculation and N/A value if any error occurs in that calculation. |
Understanding calculated columns and measures
Calculated Columns
Calculated column is just like any other column in a table. It is created within the data model and are calculated row by row at the column level. A calculated column is computed during the database processing and then stored in the data model. All calculated columns occupy space in memory and are computed during table processing. You can use it in row, columns, filters, or values of a pivot table or any other report. A calculated column is usually represented as Table[Column].
Measures
Measures, as the name suggests are aggregates and DAX language helps with creating additional measures based on the data model to help with reporting/visuals. You can consider a user-defined formula as a measure where that measure does dynamic aggregation and gets evaluated in the context of the cell of the Pivot Table or DAX query. It does not occupy memory in data model however, it consumes additional CPU at the time of query. A measure is simply represented as [Measure].
Difference between calculated columns and measures
| Sr.No | Calculated Column | Measure |
|---|---|---|
| 1 | Computed during data refresh and uses the current row as a context; it does not depend on user activity on the pivot table. | Compute during query execution and gets evaluated in the context of the cell of the pivot table or DAX query. |
| 2 | Occupy space in memory and are computed during table processing. It uses the precious RAM. | It neither gets stored in data model nor occupy the precious RAM. However, it utilize the CPU. |
| 3 | Always gets created in a table and belongs to that table. | It needs to be defined in a table however, it does not really belong to the table. You can move a measure from one table to another one without losing its functionality. |
