Another short blog.
Ok you are working with Power View in Excel and all over sudden you get an error message in Power View that doesn’t help at all:
“Power View was not able to complete the action due to a query failure. This can happen if the structure of your data has changed, in which case closing and re-opening the application will resolve the problem.”
and the Error details says:
<Message>Query couldn’t run for dataset ‘DATASET’.</Message>
It looks you are stuck here as there are no more details, but there is a way to see what is actually going on. You can capture the Power Pivot data model engine trace of everything that is send to the engine and what the engine returns. Go to the Power Pivot tab , click the settings button and enable the profiler, a trace will be saved on your machine:
Now make sure you installed SQL Server Profiler, it comes with SQL Server Management Studio, in order for you to open the file.
This opens a very detailed trace file, I ran this one when refreshing a single Power View sheet:
Thanks to this trick the person I was working with was able to figure out that even though his measures worked when testing it they didn’t work in some cases but Power View didn’t give the error message in all cases.
By the way this is also a great way to dive deep into what DAX actions happen under the covers as it also works with Excel Pivot Tables and MDX.
Happy Holidays 🙂