When you’re dealing with a beast like DAX you can use any help there is, right? So here I show you how you can debug DAX variables who contain tables or show the result of multiple variables at once. So you can easily compare them with each other to spot the reason for problems fast.
Please note, that currently only comma separated DAX code is supported.
Example
Watch this measure from Gerhard Brueckl’s brilliant solution for dynamic TopN clustering with others. It contains 5 variables who return tables and one variable with a scalar:
Measure with variables who contain tables and scalars
If you want to follow along how this calculation is evolving for each value in a matrix, my VarDebugMeasure will show details of every variable like so:
Measure to debug DAX variables
Method
This method is a variation of my previous blogpost, which made the elements of tables in a CALCULATE function visible. Cool thing with the new function for variables is, that you can see details of all your variables in one measure (see picture above).
Code to debug DAX variables
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| let func = | |
| let | |
| Source = (columnNames as text, optional topN as number, optional measureCode as text) => | |
| let | |
| /* Debug parameters | |
| measureCode = DAXMeasureCode, | |
| columnNames = "RankItem,RankMeasure, null, Rank, TopOrOthers, TopN_Others", | |
| topN = 5, | |
| End of debug parameters */ | |
| MeasureCode = if measureCode = null then DAXVariableMeasureCode else measureCode, | |
| ColumnNames = List.Transform(Text.Split(columnNames, ","), (x) => [Cleaned = Text.Trim(Text.Clean(x)), Result = if Cleaned = "null" then null else Cleaned][Result]), | |
| TopN = if topN = null then 10 else topN, | |
| fnTextRemoveBetweenDelimiters = | |
| (TextToClean as text, StartDelimiter as text, EndDelimiter as text, optional RemoveDelimiters) => | |
| let | |
| removeDelimiters = if RemoveDelimiters = null then StartDelimiter & EndDelimiter else "", | |
| Source = Text.From(TextToClean), | |
| FirstSplit = Text.Split(Source, StartDelimiter), | |
| SecondSplit = List.Combine(List.Transform(FirstSplit, each Text.Split(_, EndDelimiter))), | |
| ListAlternate = List.Alternate(SecondSplit,1,1,1), | |
| ListSelect = List.Select(ListAlternate, each _<>""), | |
| TextCombine = Text.Combine(ListSelect, removeDelimiters) | |
| in | |
| TextCombine, | |
| fnConcatenateTableValues = | |
| (filterExpression as text, | |
| myColumnName as text, | |
| optional MaxFilters as number, | |
| optional VarName as text | |
| ) => | |
| 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, | |
| FullFilterExpression = filterExpression, | |
| MeasureString = | |
| Text.Replace(VarName, Text.Start(VarName,4), "VAR Debug") & " = | |
| 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, | |
| measureCodeWithoutMultilineComments = fnTextRemoveBetweenDelimiters(MeasureCode, "/*", "*/", "Yes"), | |
| ConvertMeasureCodeToList = Text.Split(measureCodeWithoutMultilineComments, "#(lf)"), | |
| ExtractMeasureName = Text.Trim(Text.BeforeDelimiter(ConvertMeasureCodeToList{0}, "=")), | |
| ConverteMeasureCodeListToTable = Table.FromList(ConvertMeasureCodeToList, Splitter.SplitByNothing(), null, null, ExtraValues.Error), | |
| EliminateComments = Table.AddColumn(ConverteMeasureCodeListToTable, "Custom.1", each Text.BeforeDelimiter([Column1], "//")), | |
| CleanupExtractedCode = Table.TransformColumns(EliminateComments,{{"Custom.1", Text.Trim, type text}}), | |
| Cleansup2 = Table.TransformColumns(CleanupExtractedCode,{{"Custom.1", Text.Clean, type text}}), | |
| FilterOutEmptyRows = Table.SelectRows(Cleansup2, each ([Custom.1] <> "")), | |
| ExtractVARandRETURNRows = Table.AddColumn(FilterOutEmptyRows, "VarReturn", each if Text.Upper([Custom.1]) = "RETURN" then "RETURN" else if Text.Start(Text.Upper([Custom.1]),3) = "VAR" then Text.BeforeDelimiter([Custom.1] , "=") else null), | |
| FillDownVARandRETURNRows = Table.FillDown(ExtractVARandRETURNRows,{"VarReturn"}), | |
| FilterOutNonRelevantRows = Table.SelectRows(FillDownVARandRETURNRows, each ([VarReturn] <> null) and (Text.Upper([Custom.1]) <> "RETURN")), | |
| GroupByVARandRETURN = Table.Group(FilterOutNonRelevantRows, {"VarReturn"}, {{"Code", each Text.Combine(_[Custom.1], "#(lf)") }}), | |
| ExtractVARCode = Table.AddColumn(GroupByVARandRETURN, "VarCode", each Text.AfterDelimiter([Code], "="), type text), | |
| VarTable = Table.SelectRows(ExtractVARCode, each (Text.Start(Text.Upper([VarReturn]), 4) = "VAR " )), | |
| AddColSelectionsColumn = Table.FromColumns( Table.ToColumns(VarTable) & {ColumnNames} , Table.ColumnNames(VarTable) & {"ColSelections"}), | |
| CheckIfColumnNameOrScalar = Table.AddColumn(AddColSelectionsColumn, "JustColumnNames", each Value.Type([ColSelections]) = type text), | |
| VARDebugCode = Table.AddColumn( | |
| CheckIfColumnNameOrScalar, | |
| "Custom", | |
| each if [JustColumnNames] | |
| then fnConcatenateTableValues([VarCode], [ColSelections], TopN, [VarReturn]) | |
| else Text.Replace( | |
| [VarReturn], | |
| Text.Start([VarReturn],4), | |
| "VAR Debug") | |
| & " = " & [Code] & "#(lf)" & "RETURN" & "#(lf)" & Text.RemoveRange([VarReturn],0,4) | |
| ), | |
| CleanupCode = Table.TransformColumns(VARDebugCode, {{"VarReturn", each Text.Trim(Text.AfterDelimiter(_, " ")), type text}}), | |
| CleanupTable = Table.SelectColumns(CleanupCode,{"VarReturn", "Code", "Custom"}), | |
| CreateTextValue = "VARDebugMeasure =" & "#(lf)" & Text.Combine(CleanupTable[Code], "#(lf)") & "#(lf)" & "RETURN" & "#(lf)" | |
| & "IF(ISBLANK([" & ExtractMeasureName & "]), BLANK(), #(lf) —– #(lf)" | |
| & Text.Combine(CleanupTable[Custom], "#(lf) #(lf)") & "RETURN #(lf)" | |
| & Text.Combine(List.Transform(CleanupTable[VarReturn], each """" &_ & ": "" & IFERROR(Debug" & _ & ", BLANK())"), "#(lf) & UNICHAR(10) & ""———————-"" & UNICHAR(10) & ") & "#(lf) —– #(lf))" | |
| in | |
| CreateTextValue | |
| in | |
| Source , | |
| documentation = [ | |
| Documentation.Name = " DAX.VariableDebugger.pq ", | |
| Documentation.Description = " Produces DAX code to debug the variables in a DAX measure. ", | |
| Documentation.LongDescription = " Produces DAX code to debug the variables in a DAX measure. See https://wp.me/p6lgsG-29U for details ", | |
| Documentation.Category = " DAX ", | |
| Documentation.Source = " www.TheBIccountant.com . https://wp.me/p6lgsG-29U . ", | |
| Documentation.Version = " 1.2: Bugfix for multirow-comments ", | |
| Documentation.Author = " Imke Feldmann: www.TheBIccountant.com. https://wp.me/p6lgsG-29U . ", | |
| Documentation.Examples = {[Description = " ", | |
| Code = " ", | |
| Result = " "]}] | |
| in | |
| Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation)) |
Code parameters
Because one cannot paste text with linebreaks into the function dialogue in the query editor, I opted for a separate query that holds the code from the measure with the variables to investigate. If you name it “DAXVariableMeasureCode” like the default in the function, all have to do is to fill in the first parameter of the function like so:
- A comma separated text string that lists one value for each variable: If the variable represents a table, then the column name whose values shall be shown. For scalars, the null-value has to be entered.
- Optional: Number for how many sample values shall be shown. Default value is 10. So if you want to change it, just put in a different number in here.
- Full code of the measure that includes the variable (including the measure name)
6 steps to wow
- Copy the function code:
- Create a new query in the query editor and replace the existing code with the copied code (Strg+A, Strg+V)
- Create another new query (named “DAXVariableMeasureCode”) where you paste the DAX-code of the measure that contains the variables.
- Call the function with the parameters described under “Code parameters”. In my example, this looks like so:
Call function
- Copy the resulting DAX code
Copy DAX function code
- Create new measure where you paste the copied DAX code
- Drag that measure into a table or matrix beside the original measures
Please follow along these steps in this video:
You can download the file with examples here: Debug DAX Variables
Enjoy & stay queryious 😉
Pingback: Text.RemoveBetweenDelimiters function for Power BI and Power Query – The BIccountant