In this video we discuss the fundamentals of DAX, a powerful formula language used in Power BI.
Experienced Power BI users swiftly change between Power Query’s M language and DAX, depending on tasks at hand. We will not be getting into the nitty-gritty details of either programming languages; but for the sake of simplicity you may distinguish between the two based on the stages of your report.
If you are importing, cleaning, and transforming your data in Power Query then you are using the M language. After that is done and you are about to start analyzing information and ask questions using your data, then you are in the DAX territory.
Watch our video for a more detailed introduction to DAX.
At the level of a business user in Power BI, you are going to use DAX in two main ways: creating calculated columns and measures.
Calculated columns are formula-based columns that are created as the extension of your table. These columns are evaluated for each row and created based on data that has already been loaded into a data model.
Measures are formulas built in DAX which create dynamic values depending on the context and filters. Unlike calculated columns, measures do not live in tables and do not create columns. Therefore, many analysts prefer measures because they do not take up extra space in a data model.
Comparison of DAX and Excel
In fact, DAX was first introduced in Power Pivot. This means if you are a strong Power Pivot user then you are already familiar with DAX. For those who did not get to use Power Pivot, many DAX formulas will still remind Excel. The transition from one to another would not be hard.
There is one important factor to remember. This goes back to the difference between the spreadsheet cell-based tabular model and column-based table model. Excel performs calculations over cells, where each cell has its unique coordinates. DAX, on the other hand, works only on columns and tables.