If you are looking at a visual on your page in Power BI and the numbers don’t seem quite right, you can use Performance Analyser within Power BI to copy the DAX query that is building the visual. Once you have the DAX query in Tabular Editor you can debug it. If you haven’t used Performance Analyser, watch this link from Guy in a Cube. Equally if you prefer to watch a video rather than read, here is the link where I explain the blog post.
Once you have the Query copied into your clipboard, head into Tabular Editor connect to your Power BI model either directly using External Tools or from within Tabular Editor. This link from Enterprise DNA gives details on how to do this and open a new DAX query.
A word of caution, when you use performance analyser to capture the DAX query, particularly with a Matrix or with Totals, the query can look quite complex.
If I want to capture the DAX query using performance analyser, it’s often useful to visualise the data as a table and remove the totals.
A simpler method of drilling down into your calculations, is to use a pivot table within TE3.
Now that you have a table of the query, you can debug from within the cell that you want to investigate.
Once you click on debug this value a new window will open that shows you the measure that are we immediately evaluating.
Within the Evaluation Context window, you can tick on and off to select the filters and see how the number changes.
You can use the F10 Step into button which allows you to see how the measure is processed. This is a simple measure using SUMX which requires a Table as it’s first argument so as you press F10, the measure moves to the first part, the Sales table. The next part of the measure looks at the expression within SUMX, this is Sales[Quantity] * Sales[Net Price] and you can see in the Locals window the values that the expression is showing within the current evaluation context.
Now this is a simple measure but as we work through each step of the evaluation we can see exactly what is being calculated and the value associated within the Local Window.
The Call tree window is also useful to see how the measure is evaluated as you can see the breakdown of each step:
Where the debugger starts to be really helpful is when Calculate is used within a Measure. This is because calculate changes the filter context that the measure is defined in.
You can use F10 to step through each full line of the Expression, but when you want to go further into the Expression you use F11 which allows you to go into more detail for each part of the Expression.
The Call Tree shows a breakdown of the DAX code for each separate entry for each DAX expression. if you know which area you want to examine, you can double click within the Call Tree and it will give you the result in Locals.
The Evaluation Context – shows everything that happens within the cell and has an impact upon it – what makes it different from the cell next to it for example.
The current context is impacted upon what is highlighted in yellow at the top of the DAX debugger
Locals – tries to tell you what is relevant for you now.
I hope you’ve found this useful and has given you confidence to have a go. Any comments, please feel to reach out. Link to YouTube below.