{"id":3833,"date":"2021-04-05T13:30:00","date_gmt":"2021-04-05T11:30:00","guid":{"rendered":"https:\/\/thebipower.fr\/?p=3833"},"modified":"2022-10-02T14:50:32","modified_gmt":"2022-10-02T12:50:32","slug":"dax-debugger","status":"publish","type":"post","link":"https:\/\/thebipower.fr\/index.php\/2021\/04\/05\/dax-debugger\/","title":{"rendered":"DAX debugger in Power BI Sidetools"},"content":{"rendered":"\n

DAX debugger is meant to help Power BI report developers displaying sample data from a DAX formula. Measures, calculated columns and calculated tables can be debugged very easily without impact on calculation performance. An advanced mode allows user to fine tune how data is displayed.<\/p>\n\n\n\n

This demo will use the Contoso model provided by Microsoft here : https:\/\/www.microsoft.com\/en-us\/download\/details.aspx?id=46801<\/a><\/p>\n\n\n\n

Simple debug mode<\/h2>\n\n\n\n

As a beginning, the examples will debug a measure. DAX debugger will work almost the same with calculated columns and caculated tables.<\/p>\n\n\n\n

\"DAX<\/figure>
\n

Simply type the measure name and the table name against you want to evaluate the measure. Intellisense will propose what is available in the model.
Then click on the green ‘Play’ button to start the debugging session.<\/p>\n<\/div><\/div>\n\n\n\n

Lets start writing a very simple measure :<\/p>\n\n\n\n

Sales lines = \nCOUNTROWS ( Sales )\n\n<\/code><\/pre>\n\n\n\n
\n
\n
Then lets ask the debugger to display the measure in the sales table.
The ‘Play’ button turns to red to let you stop the debugging session.<\/p>\n<\/div>\n\n\n\n
\n
\"DAX<\/figure>\n<\/div>\n<\/div>\n\n\n\n
\"DAX
The measure is displayed in a debug table. The debug context is the ‘Sales’ table<\/figcaption><\/figure>\n\n\n\n
You can move columns and sort by any column. You can select cells and copy using CTRL C then you can paste it anywhere using CTRL V.<\/p>\n\n\n\n
Tabs on the left side allow you to view query properties and error.
For performance purpose, the debugger displays a subset of the whole result. Lines number is limited to 30 by default but you will be able to change this limit. <\/p>\n\n\n\n
Now, lets say you want to display the measure in ‘Channel’ table.<\/p>\n\n\n\n
\"DAX<\/figure>
\n
No need to stop the debugger. You just need to modify the table.<\/p>\n<\/div><\/div>\n\n\n\n
\"DAX<\/figure>
\n
The debug table is modified automaticaly<\/p>\n<\/div><\/div>\n\n\n\n
A very good practice writing DAX is to use variables. DAX debugger will help you even more if you do so.<\/p>\n\n\n\n
Sales lines = \nVAR Var1 = COUNTROWS ( Sales )\nVAR Var2 = 2 * Var1  \nRETURN Var1<\/code><\/pre>\n\n\n\n
\"DAX<\/figure>
\n
As soon as you validate the measure in Power BI desktop, variables are displayed close to the measure<\/p>\n<\/div><\/div>\n\n\n\n
\n
\n
If your variable is a table, it will be displayed in an other window.<\/p>\n<\/div>\n\n\n\n
\n
Sales lines = \nVAR Var1 = COUNTROWS ( Sales )\nVAR Var2 = 2 * Var1  \nVAR Var3 = FILTER( 'Product', 'Product'[BrandName] = \"Contoso\")\nRETURN Var1<\/code><\/pre>\n<\/div>\n<\/div>\n\n\n\n
In the simple debug mode, the specified table will be the debug context. You may fine tune the debug context and other parameters in the advanced mode.<\/p>\n\n\n\n
Advanced debug mode<\/h2>\n\n\n\n
You have the possibility to give instructions to the DAX debugger using keywords and DAX variables.<\/p>\n\n\n\n
Modify displayed rows limit<\/h3>\n\n\n\n
\n
\n
The simplest instruction is DBG_ROWS_LIMIT in order to change the maximum rows number displayed in a debug table.<\/p>\n<\/div>\n\n\n\n
\n
Sales lines = \nVAR Var1 = COUNTROWS ( Sales )\nVAR DBG_ROWS_LIMIT = 2 <\/strong>     \nRETURN Var1<\/code><\/pre>\n<\/div>\n<\/div>\n\n\n\n
\n
\n
You can also comment debug instructions so they will have no impact on your model. The debugger will still read these instructions.<\/p>\n<\/div>\n\n\n\n
\n
Sales lines = \nVAR Var1 = COUNTROWS ( Sales )\n\/\/VAR DBG_ROWS_LIMIT = 2<\/strong>      \nRETURN Var1<\/code><\/pre>\n<\/div>\n<\/div>\n\n\n\n
\n
\n
You may hide instructions to the debugger adding an other slash.<\/p>\n<\/div>\n\n\n\n
\n
Sales lines = \nVAR Var1 = COUNTROWS ( Sales )\n\/\/\/VAR DBG_ROWS_LIMIT = 2<\/strong>      \nRETURN Var1<\/code><\/pre>\n<\/div>\n<\/div>\n\n\n\n
\"DAX<\/figure>
\n
If you don’t remember instructions, you may find them in the snippets menu.
The snippet will be copied into the clipboard. You just need to paste it within your measure and adapt it as you want.<\/p>\n\n\n\n
\/\/VAR DBG_ROWS_LIMIT = YourLimit<\/code><\/pre>\n<\/div><\/div>\n\n\n\n
Your instructions may be kept in the formula so the next time you want to debug the measure, you just need to run the debugger.<\/p>\n\n\n\n
Modify the debug context<\/h3>\n\n\n\n
Since now, we used the selected table as a debug context. You may define define it as you like with a DAX formula which might be simple or complex.<\/p>\n\n\n\n
\n
\n
Add a DBG_MAIN_CONTEXT instruction to setup the debug context and eventually add a filter.<\/p>\n\n\n\n
Feel free to modify the formula as long as it returns a table.<\/p>\n<\/div>\n\n\n\n
\n
Sales lines = \nVAR Var1 = COUNTROWS ( Sales )\n\/\/VAR DBG_MAIN_CONTEXT = FILTER( 'Product', 'Product'[ClassName] = \"Deluxe\" ) <\/strong>   \nRETURN Var1<\/code><\/pre>\n<\/div>\n<\/div>\n\n\n\n
\n
\n
Add a DBG_MAIN_SORT_ORDER instruction to define an order. Use ASC or DESC.<\/p>\n\n\n\n
DBG_MAIN_SORT_ORDER cannot be used without DBG_MAIN_CONTEXT.<\/p>\n<\/div>\n\n\n\n
\n
Sales lines = \nVAR Var1 = COUNTROWS ( Sales )\n\/\/VAR DBG_MAIN_CONTEXT = FILTER( 'Product', 'Product'[ClassName] = \"Deluxe\" ) \n\/\/VAR DBG_MAIN_SORT_ORDER = 'Product'[UnitCost] DESC  <\/strong>   \nRETURN Var1<\/code><\/pre>\n<\/div>\n<\/div>\n\n\n\n
Add other windows to display more tables<\/h3>\n\n\n\n
Add a DBG_TABLE_xxx instruction to display another table. Replace xxx with a unique name. You may add several DBG_TABLE_ instructions as long as the table name is different.<\/p>\n\n\n\n
Sales lines = \nVAR Var1 = COUNTROWS ( Sales )\n\/\/VAR DBG_TABLE_Sales = TOPN(30, 'Sales')\n\/\/VAR DBG_TABLE_Locations = ADDCOLUMNS( VALUES('Geography'[ContinentName]), \"Lines\", [sales lines] )<\/strong>\nRETURN Var1<\/code><\/pre>\n\n\n\n
Actual limitations<\/h2>\n\n\n\n
At this moment, the DAX debugger will not evaluate variables when variables are nested in a condition or an iteration.<\/p>\n\n\n\n
What’s next<\/h2>\n\n\n\n
More features are planned or already under test :<\/p>\n\n\n\n