DAXDAX Trainer

DAX Concept

Optimizing DAX Measures with Variables for Readability and Performance

Learn to use DAX variables to break down complex formulas into logical, readable steps, improving both maintainability and understanding of measures.

BeginnerVARCALCULATEREMOVEFILTERSDIVIDECOUNTROWS

Concept Breakdown

DAX variables (declared using the `VAR` keyword) allow you to store the result of an expression and reuse it multiple times within a single measure. This practice significantly enhances the readability of complex DAX formulas, making them easier to debug and understand. Variables can also improve performance by calculating a value once and referencing it, rather than recalculating the same expression multiple times.

What You Will Learn

  1. Understand when to use VAR, CALCULATE, REMOVEFILTERS, DIVIDE, COUNTROWS in a Power BI model.

  2. Practice the concept inside a real PBIX report rather than only reading syntax.

  3. Complete a beginner-level task and verify the result with a hidden answer check.

Practice in Power BI

The starter PBIX link has not been attached yet. The student workflow is ready for the admin to connect the file.

  1. Download and unzip the provided 'Tallest Buildings - Variables.pbix' file and open it in Power BI.

  2. Observe the existing matrix displaying a count of buildings for each country and city.

  3. Create a new measure named 'Count of Buildings % of Country'.

  4. Inside this measure, declare a variable (e.g., 'CountBuildings') to store the total count of rows in the 'Building' table using `COUNTROWS('Building')`.

  5. Declare a second variable (e.g., 'CountBuildingsNoCityFilter') that counts the number of buildings but removes the filter applied by the 'City' field. Use `CALCULATE(COUNTROWS('Building'), REMOVEFILTERS(Building[City]))` for this.

  6. Add a `RETURN` statement to the measure, dividing the first variable by the second variable (e.g., `DIVIDE(CountBuildings, CountBuildingsNoCityFilter)`).

  7. Apply appropriate formatting (e.g., percentage) and display this new measure in the matrix.

  8. Create another new measure called '% of Old Buildings'.

  9. Within this measure, create a variable named 'CountBuildings' to store the total count of buildings using `COUNTROWS('Building')`.

  10. Create a second variable named 'CountOldBuildings' to store the count of buildings whose 'Year Opened' is before 2010. Use `CALCULATE(COUNTROWS('Building'), 'Building'[Year Opened] < 2010)` for this.

  11. Add a `RETURN` statement to divide 'CountOldBuildings' by 'CountBuildings'.

  12. Add the '% of Old Buildings' measure to the matrix and apply percentage formatting.

  13. Save and close the report.

Starter DAX

VAR MyVariable = Expression RETURN MyVariable

Expected Outcome

The matrix will display two new measures: 'Count of Buildings % of Country' showing each city's building count as a percentage of its country's total, and '% of Old Buildings' showing the percentage of buildings opened before 2010 for each category, both calculated using DAX variables.

Checking your sign-in status...