DAX CALCULATE Debugger

CALCULATE is the most powerful function in DAX, as it allows you to change the filter context under which its expression is evaluated to your hearts content. But with big number of options to choose from, often comes big frustration when the results don’t match expectations. Often this is because your syntax to modify the filter context doesn’t do what you’ve intended. Unfortunately CALCULATE only displays its result and not how it achieved it, so debugging becomes a challenge. This is where my CALCULATE Debugger measure can help out:

DAX CALCULATE Debugger

This is a measure that returns a text-value, showing the number of rows of the adjusted filter context table, the MIN and MAX value of the selected column as well as up to the first 10 values. Just place this measure beneath the CALCULATE-measure in question and try to find the error 😉

Just have in mind, that this only works for standalone CALCULATE-functions and not for those who are nested in other functions (who modify the evaluation context).

The YTD-measure is defined as follows:

YTD = CALCULATE ( [Amount], DATESYTD ( 'DimDate'[Datum] )

The code for the DAX Debugger measure looks like this:

In row 2 you fill in the filter expression from the YTD-expression (2nd argument: ‘DimDate'[Datum]). You can choose from which column the values shall be shown, just write that in rows 6, 7 and 11 ([Datum]). If you want to adjust the TOPN-figure for the sample values to be shown, replace the 10 in row 9 accordingly. If you don’t want to show sample values at all, just uncomment row 13 and comment out row 14 and 15.

Thanks to Tom Martens for providing the crucial hint of how to reference a column from a table that’s defined in a variable by using X-functions!

Further adjustments have to be made, if your filter expression uses the syntax sugar of boolean expressions like so:

CALCULATE ( [Amount], Product[Color] = "Red" )

This expression only returns a table when used as a filter argument in CALCULATE but not standalone in a DAX variable. So you’d have to translate the filter expression to the native underlying code like so:

FILTER ( ALL ( Product[Color] ), Product[Color] = "Red" )

As this cries for some automation, I’ve produced some nifty M-function that does all that autoMagically. It lives in my M-function-library so I have it at hand within PowerBI for immediate use.

The M-function

This function creates the DAX-code in the query editor. Just fill in the parameters (see below) and the DAX code will be created automatically: Just copy and paste as a new measure.

	let func =
	(filterExpression as text,
	myColumnName as text,
	optional MaxFilters as number
	) =>

	let
	/* Debug parameters
	myColumnName = "Datum",
	filterExpression = "DATESYTD(DimDate[Datum])",
	MaxFilters = 10,
	*/// End of debug parameters

	maxFilters = Text.From(if MaxFilters = null then 10 else MaxFilters),
	ListOfDelimiters = {">=", "<=", "<>", "=", ">", "<"},

	// Splits filterExpression by the first delimiter it finds
	SplittedBoolean = List.Accumulate(
	ListOfDelimiters,
	{},
	(state, current) => if List.Count(state) = 2
	then state
	else Text.Split(filterExpression, current)),
	HasSyntaxSugar = not Text.Contains(
	List.First(SplittedBoolean),
	")"),
	FullFilterExpression = if HasSyntaxSugar
	then "Filter(All(" & SplittedBoolean{0} & "), " & filterExpression & ")"
	else filterExpression,
	MeasureString = "
	DAX Debugger =
	VAR FilterTable = "
	& FullFilterExpression & "
	VAR TableLength =
	COUNTROWS(FilterTable)
	VAR ColumnMax =
	MAXX(FilterTable, [" & myColumnName & "])
	VAR ColumnMin =
	MINX(FilterTable, [" & myColumnName & "])
	VAR TOPX = IF(TableLength < " & maxFilters & ", TableLength, " & maxFilters &")
	VAR ValuesInColumn =
	CONCATENATEX(TOPN(TOPX, FilterTable), [" & myColumnName & "], "", "")
	RETURN
	–""TableLength: "" & TableLength & "", Min: "" & ColumnMin & "", Max: "" & ColumnMax
	""TableLength: "" & TableLength & "", Min: "" & ColumnMin & "", Max: "" & ColumnMax & UNICHAR(13) & UNICHAR(10) & ""Top "" & TOPX & "" Values: "" & ValuesInColumn
	"
	in
	MeasureString ,
	documentation = [
	Documentation.Name = " DAX.CalculateDebugger.pq ",
	Documentation.Description = " Produces DAX code to debug a filter argument of CALCULATE. ",
	Documentation.LongDescription = " Produces DAX code to debug a filter argument of CALCULATE. <code>filterExpression</code> holds the code of the CALCULATE filter expression, <code>myColumnName</code> the name of the column whose values to show and optional <code>MaxFilters</code> can modify the default value of the first 10 sample values to show. ",
	Documentation.Category = " DAX ",
	Documentation.Source = " www.TheBIccountant.com . https://wp.me/p6lgsG-1nD . ",
	Documentation.Version = " 1.0 ",
	Documentation.Author = " Imke Feldmann: www.TheBIccountant.com. https://wp.me/p6lgsG-1nD . ",
	Documentation.Examples = {[Description = " ",
	Code = " ",
	Result = " "]}]

	in
	Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

view raw

DAX.CalculateDebugger.pq

hosted with ❤ by GitHub

How to fill the parameters:

filterExpression: DAX-code of the CALCULATE filter expression
myColumName. Name of the column whose values to show
MaxFilter: This is a optional parameter: Fill in a different number from 10 if you want to change the default value for the TOPN selection of the sample values to be shown.

This function detects boolean expressions automatically and produces the appropriate code.

If you don’t know how to use M-function-code, please check out Ruth Pozuelo’s video.

Enjoy & stay queryious 😉

New Generation Finance – Accounting – Controlling using Microsoft BI stack

DAX CALCULATE Debugger