DAX is a complicated language; on the surface it's deceptively simple, but under the hood it can quickly become complex and can take many years to master. If you've ever typed a formula and crossed your fingers when hitting Enter, then you know what I mean. If you are reading this article, you are certainly already on your own DAX learning journey.
As you become more proficient in DAX, you will begin to write more complex formulas that behave differently based on your image filters. An example is the profit and loss report that I shared in 2020. This report contains a rather complicated formula. It was built slowly and methodically, step by step, until it worked as desired. This is the best way to write DAX. If you want to see how I do it, 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's not working properly, or maybe you need to improve it for some reason. Whatever the reason, today I share my technique for debugging complex formulas using tool tips.
Because it is necessary
DAX is difficult to debug for several reasons. First of all, 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 highly dependent on the behavior of the filter (filter context) that comes from your images. If you don't understand the context of the filter, you can't debug your formulas. In the case of the P&L example I'm using for this article today, there are 3 different filtering behaviors coming from the visual that can influence the bottom line. These are the first three variables shown below.
In this article, I'll show you how to set up the tooltip for a Matrix visual so that you can "see" what's going on with this formula through the debugging process.
Set up a tooltip for debugging
The following are the steps to set up the tooltip for debugging DAX formulas (there is a full video of all these steps further down the page).
- Create a measure for each of the intermediate variables in the DAX formula. In the above formula, I wrote measurements for the 3 variables, CalcType, DisplayDetailCode, and isSubHeaderVisible.
- Create a tooltip report page (New Page -> Layout -> Page Information -> Tooltip).
- Set the page size to fit the content of the tooltip you want to display.
- Add a Matrix visual to the tooltip page, and then add the measures that you created for the intermediate values in step 1 to Values.
- Optionally, add a text box to the tooltip page and copy the final DAX formula that you are debugging into the text box.
- Adjust the size of the tooltip page if necessary so that all content is visible.
- Name the tooltip page. I called mine as tooltip.
- Return to the page with the visual using the final DAX formula.
- If it's a table or matrix, go to Format -> Tooltip. Create the tooltip, select the tooltip type as the report page, and then provide the name of the tooltip page in the placeholder.
That's it. Now you can hover over the values in the Matrix and look at the intermediate values and the final formula to check if it is correct or not.
I have recorded the entire process that I described earlier in the following video.