Debugging DAX with Tooltips

Level: Intermediate

DAX is a tricky language; on the surface it is deceptively simple, but under the hood it can quickly become complex and it can take many years to master. If you have ever typed a formula and crossed your fingers when you press Enter, then you know what I mean. If you are reading this article, you are no doubt already on your own DAX learning journey.

As you become more competent at DAX, you will start to write more complex formulas that behave differently depending on the filters in your visuals.  One such example is the P&L report that I shared in 2020.  This report contains quite a tricky formula. It was built slowly and methodically, one step at a time, until it was working as desired.  This is the best way to write DAX.  If you want to see how I do it, then go back to the article linked above and watch the video.  While I recommend this as the best approach to writing formulas, the reality is that sometimes you will need to go back to a formula you wrote previously and debug that formula.  Maybe it is not working correctly, or maybe you need to enhance it for some reason.  Whatever the reason, today I am sharing with you my technique to debug complex formulas using tool tips.

Why This is Needed

DAX is tricky to debug for a few reasons.  Firstly, there is no step by step evaluation capability, like those that exist in VBA, Excel, and other programming languages.  The second reason is that DAX formulas are tightly dependent on the filter behaviour (filter context) coming from your visuals.  If you don’t understand the filter context, you cannot debug your formulas.  In the case of the P&L example I am using for this article today, there are 3 different filtering behaviours coming from the visual that can influence the final result.  These are the first three variables shown below.

In this article I will show you how to set up Tooltips for a Matrix visual so you can “see” what is going on with this formula through the debugging process.

Setting up a Tooltip for Debugging

Following are the steps to set up Tooltips for debugging the DAX formulas (there is a full video of all of these steps further down the page).

1. Create a measure for each of the intermediate variables in the DAX formula. In the above formula, I wrote measures for the 3 variables, CalcType, DisplayDetailCode and isSubHeaderVisible.
2. Create a tooltip report page (New Page -> Format -> Page information -> Tooltip).
3. Set the page size to suit the tooltip contents you want to display.
4. Add a Matrix visual to the tooltip page and then add the measures you created for intermediate values in step 1 to Values.
5. Optionally, add a Text box to the tooltip page and copy your final DAX formula that you are debugging to the text  box.
6. Adjust the tooltip page size if required so that all the content is visible.
7. Give the tooltip page a name. I called mine as Tooltip.
8. Switch back to the page with the visual using the final DAX formula.
9. If it is a Table or a Matrix, go to Format -> Tooltip. Make the tooltip on, select tooltip type as report page and then give the name of your tooltip page in the placeholder tage.

That’s it. You can now hover over the values in the Matrix and observe the intermediate values and the final formula to check if it is correct or not.

Video

I have recorded the entire process that I outlined above in the following video.