Excel Formulas vs DAX Formulas in Power BI

In a comprehensive tutorial from Pragmatic Works, the similarities and differences between Excel formulas and DAX formulas in Power BI are explored to help users build confidence when working with both tools.

What is DAX?

DAX, or Data Analysis Expressions, is a formula language specifically designed for Power BI, Power Pivot, and Analysis Services. It was created to help users create powerful data models, perform complex calculations, and make data-driven decisions.

Key uses of DAX:

• Create calculated columns.
• Generate entire tables.
• Develop measures for reusable calculations.

Writing Formulas in Excel vs. DAX

While both Excel and DAX involve formulas, there are key differences in how they operate:

• Excel: Formulas are applied directly to individual cells using cell references (e.g., =SUM(A1:A10)).
• DAX: Formulas operate on entire columns or fields within a dataset, making them more dynamic.

To start writing DAX formulas in Power BI:

1. Click on the "New Measure" button in the Home ribbon or Modeling tab.
2. A formula bar will appear where you can write your expression.
3. The formula bar can be expanded using Ctrl + Mouse Wheel for better visibility.

Creating a Simple DAX Formula

To create a measure in DAX that sums sales data, the structure is similar to Excel's SUM function:

Sales Total = SUM('Internet Sales'[Sales Amount])

Breaking it down:

• Sales Total: The name of the measure.
• SUM: The function being used.
• 'Internet Sales'[Sales Amount]: Table and column being referenced.

Formula Differences Explained

Key distinctions between Excel formulas and DAX include:

• Cell vs. Column Reference: Excel formulas typically reference individual cells, while DAX references entire columns.
• Data Context: DAX relies on "row context" and "filter context" to dynamically adjust calculations based on visual elements and filters.
• Formula Application: Excel formulas apply to cells immediately, while DAX measures are stored calculations applied dynamically.

Using IntelliSense in DAX

DAX offers a powerful feature called IntelliSense, which suggests functions and helps reduce errors while writing formulas. Key benefits include:

• Function suggestions based on initial typing.
• Syntax guidance for functions and arguments.
• In-line descriptions to assist with proper formula construction.

Context in DAX: Row and Filter Context

Row Context: Applies when a formula iterates through each row in a table. Example: Creating a calculated column.

Filter Context: Applies when calculations adjust based on visual filters or slicers in a Power BI report.

Example: A measure summing sales filtered by product category or date range.

Excel and DAX: Similarities Recap

• Both use functions like SUM and AVERAGE.
• Both require formulas to be written with proper syntax.
• Both support dynamic calculations, though DAX focuses on columnar data.

Key Takeaways

• DAX formulas and Excel formulas share similar structures but differ in scope and context.
• Excel focuses on individual cell references, while DAX uses entire columns for calculations.
• DAX introduces row and filter context, adding more power to data analysis.

To master DAX and Excel together, explore Pragmatic Works' extensive training library with dedicated courses for both tools. Whether you're an Excel pro or just starting in Power BI, these lessons will help you build more efficient data models and make smarter business decisions.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on DAX, Excel and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.