DAX Explained for Beginners: Going Beyond the Power BI UI

Data Analysis Expressions (DAX) is a formula language used in Power BI that gives you more controlled calculations than the standard UI. While the UI provides pre-built functions, DAX provides more advanced data analysis and modelling.

The Power BI UI allows the user to perform basic data manipulation like sum and count, create visualisations like bar charts and line graphs, and build reports using drag-and-drop features. However for more complex calculations and dynamic data analysis, DAX is essential.

1. Advanced Calculations

DAX can be used to create custom measures which is one single variable or calculated columns which is an added column to your dataset. Measures and calculated columns can have logic such as yearly growth, moving averages, grouped summary, and dynamic rankings which cannot be added using the UI alone.

2. Dynamic Filtering

DAX can manipulate filter contexts to create measures that respond dynamically to user input and interaction with the report. This provides more tailored insights.

3. Data Modelling

DAX helps with stating relationships between tables in your data model to create a relational database, enhancing the depth of analysis within Power BI.

Example: Sum Grouped by Year-to-Date

You’ve been running a ramen shop for a few years now and you want to take a look at your overall sales trend over the years. You want to summarise your sales data into a sum of the number of sales grouped by the year-to-date. You need DAX for this precise calculation.

YTD Sales = CALCULATE(
    SUM(Sales[TotalAmount]),
    DATESYTD(Sales[Date]))