Overview
This section is about getting familiar with DAX, along with some important things one must know before working with DAX.
Explanation
What Does DAX Stand For?
DAX stands for Data Analysis Expressions, which is a language to facilitate data analysis by querying Data Model(s).
According to Microsoft’s documentation, DAX helps you create new information from data already in your (Data) model. Moreover, DAX can solve analytical problems ranging from creating a basic calculation to performing complex time-based data comparisons.
DAX is also called a formula language or a functional language, where requirements are expressed as formulas or functions.
Is Learning DAX Really Important?
According to Eugene Meidinger (Database Developer and DAX Trainer), If you are doing BI (Business Intelligence) with Microsoft, you need to learn DAX.
What Does DAX Normally Do?
DAX is used to create calculated columns or measures in the tables of a (Tabular) Data Model.
What is a Calculated Column?
A calculated column is a column that is added to a table in the Data Model using DAX query.
For example, we can create Full Name column by concatenating First Name and Last Name columns of a table in the Data Model.
However, we need a DAX query to concatenate two columns.
What is Measure?
A measure in its simple form is a calculation created using DAX formula such as creating Total Sales measure by summing up all the Sale Amount values in the column.
Uses of DAX
DAX is used for data analysis, comparisons and visualization in the following Microsoft technologies:
Power BI
Power BI is Microsoft’s latest cloud powered business intelligence technology for analyzing and visualizing data in the form of reports and dashboards.
Analysis Services Tabular Models
Tabular Models are Analysis Services databases which either run in-memory or in direct query mode optimized for data analysis and are deployed to either Analysis Services Database Server or Azure Analysis Services.
Power Pivot (Excel)
Power Pivot in Excel is capable of processing millions and millions of rows and is just like a mini data warehouse based on data model which can be queries using DAX.
Azure Analysis Services
Finally, Azure Analysis Services the cloud-based version of Analysis Services makes use of DAX to run DAX queries including creating calculated columns and measures.
DAX and Data Analysis
DAX is specifically designed to meet business analysis needs for tabular modelled data systems. So, it is designed keeping in mind the points of interest in the context of business analytics. For example, you can sum up millions of values into a single result in a flash which is also called Aggregations or you can quickly get total products purchased by region or time which is called Filtering.
DAX vs Excel Formulas
Please remember DAX may look similar to Excel formulas but it is a lot more than just formulas while EXCEL formulas are applied on cells DAX formulas are applied on tables in the Data Model.
DAX Limitations
DAX just like any other language has limitations which can be easily observed when dealing with the following situations:
- Tables with a lot of columns slow down DAX performance
- Reports showing plain long lists of records (day to day operations) without the need for any measure or calculated column
- Many to Many Relationships are not directly supported by DAX
The above limitations are based on the information shared by Eugene Meidinger (Database Developer and DAX Trainer).
DAXing Data
Daxing data means running DAX queries against a Tabular Model to either solve analytical problems in order to meet business requirements or to explore DAX further just for fun or research purposes.
