If you’re working in Power BI Desktop you may sometimes find that your visuals error with the message “Couldn’t load the data for this visual. The operation was cancelled because of locking conflicts”:
Why is this happening? If you’re an old Analysis Services person like me you may be familiar with the error – the basic problem is the same – but here’s a simple explanation. If you’re making changes to your dataset (for example editing the DAX for a measure) in Power BI Desktop then Power BI has to wait for any DAX queries, that is to say the queries that get the data for your visuals, that are currently running to finish before it can save those changes. However if it has to wait too long to do this then it will kill any queries still running so it can go ahead and commit those changes, and when it does so you’ll see the “locking conflicts” error.
I was able to recreate this error by creating a DirectQuery dataset with a single table based linked to a SQL query that takes one minute to run, building the report shown in the screenshot above, and then creating a new measure when the visual on the left was rendering. Even then it didn’t error consistently – which I guess is a good thing!
Now you know the cause, the next question is what can you do to avoid it? Since the problem is caused by long-running DAX queries the answer is to tune your queries to make them faster. To be honest, if you have queries that are slow enough to cause this error you already have a usability issue with your report – most DAX queries should run for no more than a couple of seconds.
In my scenario I am using Direct query and the measure definition is also simple like sum/distinct count. Still I am facing the locking conflict issue. How can we resolve the issue?
You will have to tune your data source or use Import mode
Hey Chris,
Thank you for your great post.
I currently retrieve data from Excel Data Model — from SSAS (OLAP cubes) using MDX queries. I sometimes get this conflict error and based on your post, I assume my current query runs before it re-runs the changes.
1. Do you have any post related to Excel Data Model querying SSAS using MDX?
2. My dataset is usually close to million rows. The refresh is fast say 2 to 5 mins but the moment when I need to add an option to one of the filters it goes very slow?
3. I see there is a parameter option in the Data Model — Table Design window but it does not allow to provide parameter when you check that?
Kindly advise and correct me if I am wrong.
Thanks & Regards,
Bhuvanesh Babu