Facts on DAX for Excel and Power BI

Philip Wiest, Guest Blogger

Take your data analysis to a new level of sophistication. DAX (Data Analysis Expressions) is the formula language of Excel Power Users, created for Power BI and Power Pivot. It enables you to create powerful data models, perform calculations and make better data-driven decisions.

There is a subtle yet important difference between using DAX for calculated Columns and using DAX for explicit measures.

If this is a foreign language to you, relax, you’ve come to the right place for facts on DAX.

If using DAX is new to you, and you’ve been struggling with slow performing reports and incorrect aggregate calculations, you need to review the features of Excel that have been in place since 2010.

Use Excel’s newest shortcuts and solutions in your inherited workbooks

For example, when students inherit data designed like Figure 1 - tblSales, they are inclined to build more columns to calculate line totals for Net Price and Cost and then, in turn, use those columns to calculate margin and margin %.

In DAX you could create the following calculated columns:

LINE TTL =	LINE TTL = tblSales[NetPrice]*tblSales[Quantity]
LINE COST =	LINE COST = tblSales[Quantity]* tblSales[UnitCost]
NET PRICE MARGIN =	NET PRICE MARGIN = tblSales[LINE TTL]-tblSales[LINE COST]
NET MARGIN PCT =	NET MARGIN PCT = (tblSales[LINE TTL]-tblSales[LINE COST])/tblSales[LINE TTL]

Table 1 - Calculated Columns

And doing that on a fact table would be a rookie mistake.

FACT TABLES and DIMENSION TABLES

Again, you need to know your fact tables from your dimension tables and tblSales is a classic fact table that is often thousands (if not millions) of rows tall. When you solve your reporting problem by adding calculated columns to a fact table you are expanding the table with each new column – taking up more memory and, with each refresh, slowing performance.

Aggregating Your CALCULATED COLUMNS Leads to More Trouble

When building your pivot table, you could aggregate those calculated columns (by Category) with Excel’s “Sum of” Implicit calculations (A)… or you could create your own aggregate expressions (B) that would do the same without the needed creation of calculated columns.

Remember, a smaller table is a faster table.

*Figure 3 - The Report with Implicit Measures (A) or Explicit Measures (B).*

If you tried to aggregate your calculation for Margin Pct, you would wind up with something like 19,217 percent in your pivot table. Obviously wrong! Using an Expression instead would work much more easily.

It’s time to forego those calculated columns (A) and cut to the chase with explicit DAX Measures.

*Figure 5 - Calculated Columns (A) versus Explicit Measures (B).*

How to Do This Using DAX

TTL NET	TTL NET = SUMX(tblSales, tblSales[NetPrice]*tblSales[Quantity])
TTL COST	TTL COST = SUMX(tblSales, tblSales[Quantity]*tblSales[UnitCost])
MARGIN	MARGIN = CALCULATE([TTL NET]-[TTL COST])
MARGIN %	MARGIN % = DIVIDE([MARGIN], [TTL NET)

Using the SUMX DAX function enables you to simplify your semantic model by eliminating the need for calculated columns. You can build your calculations into measures rather than into columns.

Excel users from the “university of the Self-taught” often want to use DAX like Excel worksheet functions. However, in the land of DAX it’s best to leave those Excel habits behind and move into a new orbit.

To learn more FACTS on DAX and get a copy of this data, please register for an upcoming live, virtual workshop:

Excel Power Tools: Power Query and Power Pivot

Accelerated Power BI for Beginners

Excel PivotTables: From Data Prep to Power Analysis

Accelerated Microsoft Excel Certificate Program

Philip Wiest is a Microsoft Certified Trainer.

Philip Wiest

Guest Blogger

Philip Wiest is an expert software trainer, computer analyst and database consultant who knows both the intricacies of computer systems and the ways today’s professionals need to use these essential business tools. Using his experience in Microsoft Excel and Microsoft Office, as well as Windows and the Internet, Phil uses a special mix of tactful guidance and distilled observation so his audiences learn and retain the critical keystrokes, application combinations and creative processes that save time and simplify computer use.