Debugging DAX Formulas with Excel

Solving a problem in DAX can prove conceptually challenging due to the limited tools available to debug a formula. When you need to write a complex measure, you can write several measures, then combine them at the end into a single measure.

However, what if you also want to inspect what rows are included in the calculation? You can convert your measure into a query. You enter the query command either in an Excel DAX Query (if you’re using Power Pivot), or into Microsft SQL Server Management Studio (if you’re using Microsoft Analysis Services), or into a third-party client like DAX Studio which supports querying both Power Pivot and Microsoft Analysis Services Tabular models.

Create DAX Query in Excel

In this example, I’ll explain the process of converting a measure (which returns a scalar) into a query (which returns a table), using Excel’s built-in DAX editor on a Power Pivot model.

Let’s suppose you have the below measure:

=SUM ( Balances[Amount] )

Perform the steps as follows:

1. In the ribbon menu, select Data | Get External Data | Existing Connections
   
2. Select the Tables tab, then select a Data Model table under where it says “This Workbook”.
   Note that you can distinguish between Data Model tables and standard tables by the colour for the first row stripe.

   	Data Model Table
   	Standard Table

3. The Import Data screen will be displayed. Select the option “Table” and “New Worksheet”
   
4. Right click on the table, then select Table | Edit DAX. You should see a popup window that looks like the below:

   Change the Command Type from Table to DAX, then enter the DAX formula below, by modifying the formula for your measure as follows:

   • enclose the formula with EVALUATE CALCULATETABLE
   • remove the scalar function SUM and column name [Amount] so that you return the Balance table to the CALCULATETABLE function.
   • add the filter context (unless you’re retrieving the Grand Total), e.g. if you want the value for the month of Jan, then you’ll need to add Dates[Month] = “Jan” to the second argument of CALCULATETABLE

   EVALUATE
   CALCULATETABLE
   (
   	Balances,
   	Dates[Month] = "Jan"
   )
   

Practice Problem

Now that you know how to use Excel to query a Power Pivot model, let’s try and solve a real problem. You have the below dataset which shows the balance for each date and category. The RequestId column is a sequential number that identifies the upload batch – the larger the RequestId, the more recent the upload occurred. If a category does not exist for a given date or upload batch, then the balance for that category is zero. This means the balance at 2-Jan-16 is 16 (comprising A and B), and the balance at 1-Feb-16 is 12 (comprising category A only).

What would be the DAX formula to calculate the closing balance regardless of context?

Incorrect Solution 1

You might be tempted to write the formula below.

Balances_Sum:=CALCULATE
(
	SUM ( Balances[Amount] ),
	FILTER
	(
		Balances,
		Balances[RequestId] = MAX ( Balances[RequestId] )
	)
)

This gives us the resulting pivot table below.

Intuitively, this sums up the most recent balance by filtering the Balances table to include only rows with the largest RequestId. But notice how the Grand Total column does not add up. The Grand Total is 12, but the categories that are being displayed are A and B which add up to 17. The pivot table should only display category A which has a value of 12, since we know from the data source that there’s no category B for Feb and so the latest balance of B should be zero instead of 5.

What is the DAX engine doing that’s giving this unintended result? The answer becomes apparent if you copy the MAX ( Balances[RequestId] ) formula segment into a separate measure called Max Request Id. You notice that the Max Request Id Grand Total for category B is 2, instead of the expected 3. This causes the DAX engine to pick up the balance that has a RequestId of 2, which was uploaded in Jan.

You can confirm this theory by running the DAX query below:

EVALUATE
CALCULATETABLE
(
	CALCULATETABLE
	(
		Balances,
		FILTER
		(
			Balances,
			Balances[RequestId] = MAX ( Balances[RequestId] )
		)
	),
	Balances[Category] = "B"
)

This gives the following result:

It’s returning a row for category B when you want it to return no rows at all.

Incorrect Solution 2

We modify the calculation that gives us the most recent RequestId by adding an ALLEXCEPT to the CALCULATE filter. The problem in the previous solution was that the formula was getting the most recent RequestId for each category, rather than the most recent RequestId for each date. Therefore, we remove all columns in the filter except Balances[BalanceDate].

Balances_Sum:=CALCULATE
(
	SUM ( Balances[Amount] ),
	FILTER
	(
		Balances,
		Balances[RequestId] =
		CALCULATE
		(
			MAX ( Balances[RequestId] ),
			ALLEXCEPT ( Balances, Balances[BalanceDate] )
		)
	)
)

We get the following result.

It’s still incorrect. Why is the balance for category A in Jan equal to 21 instead of 11? We can investigate further by executing the below query:

EVALUATE
CALCULATETABLE
(
	CALCULATETABLE
	(
		Balances,
		FILTER
		(
			Balances,
			Balances[RequestId] =
				CALCULATE 
				(
					MAX ( Balances[RequestId] ),
					ALLEXCEPT ( Balances, Balances[BalanceDate] )
				)
		)
	),
	Balances[Category] = "A",
	Dates[Month] = "Jan"
)

The query result shows that the DAX engine has summed up rows where RequestId is 1 or 2, rather than just RequestId 2.

Final Solution

We modify the previous formula so that it removes the filter on all columns in the Balance table, and adds the Dates table into the filter argument.

Balances_Sum:=CALCULATE
(
	Balances,
	FILTER
	(
		Balances,
		Balances[RequestId] =
		CALCULATE
		(
			MAX ( Balances[RequestId] ),
			ALL( Balances ),
			Dates
		)
	)
)

This gives the intended result.