All Queries Trace of Power BI Desktop not consistently working

[cymbolz]

I am attempting to capture queries from Power BI Desktop but no trace data appears in DAX Studio when expected.

I load my pbix into PBI Desktop, load DAX Studio and connect successfully. Then turn on All Queries. I Clear Cache in DAX Studio and then view my report page in PBI Desktop (navigate form a blank page) expecting the queries will be shown in the All Queries tab. But they don't appear - nothing does.

If I interact with the report (eg cross-filtering a visual) I see queries in DAX Studio.

I've even tried clearing the cache in PBI Desktop (Options) and setting the cache there to zero maximum. Am running Dec 2018 PBI Desktop and the DAX Studio 2.8.2

Please let me know what else I can provide.

[marcosqlbi]

Check this procedure: 1) Navigate to a blank page 2) Save Power BI Desktop (with the blank page selected) 3) Close Power BI Desktop 4) Open the file in Power BI Desktop (there should be a blank page) 5) Connect DAX Studio to Power BI Desktop 6) Start Trace in Dax Studio 7) Switch to Power BI Desktop 8) Move to another page that is not in the first row. At this point, DAX Studio should show you all the queries.

Let me know if it works.

[dgosbell]

Try Marco's suggestion and let us know how you go. Power BI has 2 main levels of cache:

• Visual Level
• Data Engine Level

The DAX Studio "Clear Cache" button only affects the "Data Engine Cache" while most of the time Power BI will use the higher level "Visual" cache (the datasets required by each visual on your report are cached). So once a visual has display on the report the "Data Engine" (which is what DAX Studio listens to) is not queried again unless you use a different filter combination or trigger a cross-filtering operation that has not been seen before.

[cymbolz]

@marcosqlbi It worked OK following those steps. I'm not sure why, as I did rather the same yesterday and it was not working (could be a user error, it was late in my day).

@dgosbell Is there a way to clear the visual level cache? (I'm already expecting the answer I don't want). The only method I've found is to Refresh the dataset and then the report page is re-calculated and drawn based on the trace info I see in DAX Studio after the refresh completes.

The challenge I have is in wanting to repeat the trace many times, even when no changes to the data model or data.

I did try adding or modifying a blank table and/or measure, which then seemed to trigger a new trace when I navigated back to the report page (also after clearing the (data) cache). So that might be my work-around.

[marcosqlbi]

You cannot clear the visual level cache. The technique we use:

• create an empty page
• select the empty page
• save the file
• close PBIX
• open PBIX
• activate trace from DAX Studio
• switch to the page in PBI Desktop

[dgosbell]

No we can't clear the visual cache that operates at a level above where DAX Studio connects. It broadly looks like the following:

So you also don't need to clear the data cache as the All Queries trace will capture any requests regardless of whether the data engine scans the raw data or answers from cache (the only difference will be a slight change in performance.)

This then brings me to ask why do you want to repeat the trace many times if you have not changed anything? If you haven't changed anything the queries will not change and the performance profile of them won't change.

[cymbolz]

@marcosqlbi Thanks, I'll stick to that process. Most likely I was doing something different (wrong) yesterday too!

@dgosbell My original problem arose when I couldn't get the trace at all, so I wasn't thinking too far forward. You are right, there would be some change made between tracing again. Although a scenario might be to want multiple traces to see more of a 'law of averages' but this is the best idea I can come up with.

To both of you, thank you so much for the super quick responses and diligent details. You are the reason this community is such a good place to learn from !!!!

[dgosbell]

Although a scenario might be to want multiple traces to see more of a 'law of averages'

Yes, but you will never see that from Power BI because of it's multi-layered caching. A better approach would be use Marco's approach to capture all the queries. Then run any problem queries multiple times from DAX Studio to get an average duration.

[WillAyd]

I've tried all the steps outlined in the first response to the OP but can't get this to work. Any updated guidance?

[dgosbell]

@WillAyd It's hard to say what your issue might be without knowing any details.

If you run a simple query from within DAX Studio while the All Queries trace is running like EVALUATE {123} does that get captured in the trace? If so that probably points Power BI Desktop caching things at the visual layer.

The other alternate approach with Power BI Desktop is to turn on the Performance Analyzer within Power BI, then start recording and click the refresh visuals option. You can then expand the details for the visual you are interested in and click the Copy Query option the paste the query into DAX Studio and then do your performance analysis the same as if you had captured that query using the All Queries trace