Category: Errors

Power BI Semantic Model Memory Errors, Part 4: The Query Memory Limit

Continuing my series on Power BI memory errors (see part 1, part 2 and part 3), in this post I’ll look at the query memory limit which controls the amount of memory that an individual query can consume. This is a subject which I’ve talked about in a few blog posts in the past: hitting this limit is one of the reasons you’ll see the “This visual has exceeded the available resources” error which I blogged about here and and here. There’s also some official documentation here which is pretty good.

As I mentioned in part 1 of this series, when you run a DAX query against a Power BI semantic model it will use memory. How much memory it uses will depend on your data, how you have modelled it, what data your query is requesting and how you have written any DAX calculations used by your query. For obvious reasons we at Microsoft can’t let queries use an infinite amount of memory in the Power BI Service so there is a limit – the query memory limit – on how much memory an individual query can use. This limit varies depending on whether you are using Shared (also known as Power BI Pro) or Premium/Fabric capacity, and by the size/SKU of the capacity you’re using. The different limits for each SKU are documented here.

While you can’t increase the query memory limit over the published thresholds, capacity admins can reduce the query memory limit in the admin portal by setting the “Query Memory Limit %” property on the capacity.

The default value of this property is 0, which means the maximum value for the capacity SKU you’re using will be applied; any other value will be interpreted as a percentage of that maximum value. Reducing this value can be a good way to stop your developers from using inefficient DAX in their reports and consuming too many resources on your capacity – it won’t directly reduce a query’s CU usage but DAX expressions that are very memory-intensive often use a lot of CUs too.

If the DAX query generated by a visual in a Power BI report hits the query memory limit you’ll see an error in your report. The first reaction many Power BI developers have when they hit the limit is to ask if there is a way to increase it – which you can only do by buying a larger capacity – but to be brutally honest, if you are hitting the query memory limit you have done something wrong and you need to fix your model, your DAX calculations or your report to reduce memory usage. Doing this is likely to improve report performance too. If you’re encountering the error in Power BI Desktop you can raise the limit but this is only possible in Desktop and if you do this you may end up getting the error again after you publish.

Here’s the error you’ll see in Power BI Desktop if you hit the query memory limit:

This visual has exceeded the available resources. Try filtering to decrease the amount of data displayed.

If you hit the query memory limit in a published report you’ll see this error message:

In this case you not only get the “Visual has exceeded the available resources” error but going to the details shows a more helpful message that tells you what the current query memory limit is:

Resource Governing: This query uses more memory than the configured limit. The query — or calculations referenced by it — might be too memory-intensive to run. Either simplify the query or its calculations, or if using Power BI Premium, you may reach out to your capacity administrator to see if they can increase the per-query memory limit. More details: consumed memory 1 MB, memory limit 1 MB. See https://go.microsoft.com/fwlink/?linkid=2159752 to learn more.

The error number associated with this error is 0xC13E0004 (-1052901372).

How do you know how much memory your query is using to check how near you are to the query memory limit? The new Execution Metrics event in Profiler and Log Analytics will tell you. If you find the event associated with the Query End event for the query (something I discussed here) the approximatePeakMemConsumptionKB metric gives the peak memory usage of the query reached while it was running, and it is this value that must not exceed the Query Memory Limit.

{
	"timeStart": "2024-06-21T16:24:16.608Z",
	"timeEnd": "2024-06-21T16:24:18.326Z",

	"durationMs": 1719,
	"vertipaqJobCpuTimeMs": 1078,
	"queryProcessingCpuTimeMs": 531,
	"totalCpuTimeMs": 1609,
	"executionDelayMs": 0,

	"approximatePeakMemConsumptionKB": 46662,

	"commandType": 27,
	"queryDialect": 3,
	"queryResultRows": 502
}

If you can’t use Profiler or Log Analytics there’s no direct way of knowing how much memory is using. You can however set a custom memory limit in Power BI Desktop and keep changing it until you see, or don’t see, the query memory limit and therefore estimate how much memory your query is using.

How can you reduce the amount of memory that your queries use? That’s a question that’s too big to answer in any one blog post and this one is already long enough. In my next few posts I will look at some common causes of excessive memory usage and what you can do to fix them.

Understanding The “External table is not in the expected format” Error In Power Query In Power BI And Excel

Sometimes when you’re importing data from files using Power Query in either Power BI or Excel you may encounter the following error:

DataFormat.Error: External table is not in the expected format

What causes it? TL;DR it’s because you’re trying to load data from one type of file, probably Excel (I don’t think you can get this error with any other source but I’m not sure), and actually connecting to a different type of file.

Let’s see a simple example. Say you have a folder with two files: one is an Excel file called Date.xlsx and one is a CSV file called Date.csv.

Here’s the M code for a Power Query query that connects to the Excel file and reads the data from a table in it:

let
  Source     = Excel.Workbook(File.Contents("C:\MyFolder\Date.xlsx"), null, true), 
  Date_Table = Source{[Item = "Date", Kind = "Table"]}[Data]
in
  Date_Table

Now, if you change the file path in this query – and only the file path – to point at the CSV file instead like so:

let
  Source     = Excel.Workbook(File.Contents("C:\MyFolder\Date.csv"), null, true), 
  Date_Table = Source{[Item = "Date", Kind = "Table"]}[Data]
in
  Date_Table

…you will get the “external table is not in the expected format” error shown above. This is because your code is using the Excel.Workbook M function, which is used to import data from Excel workbooks, to connect to a file that is a CSV file and not an Excel workbook. The way to fix it is to use the appropriate function, in this case Csv.Document, to access the file like so:

let
  Source = Csv.Document(
    File.Contents("C:\MyFolder\Date.csv"), 
    [Delimiter = ",", Columns = 4, Encoding = 65001, QuoteStyle = QuoteStyle.None]
  ), 
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true])
in
  #"Promoted Headers"

To be honest, if making this change is beyond your Power Query skills and you’re sure you’re trying to connect to the right file, you’re better off creating a completely new query rather than editing the query you already have.

Another common scenario where you might encounter this error is when you’re importing data from all the files in a folder and one of the files isn’t in the correct format. For example, let’s say you have a folder with three Excel files in and you use the Folder data source to import all the data from all three files:

Since all three files are Excel files the Folder option will work:

However, if you take a CSV file and drop it into the folder like so:

Then you’ll get the same error in Power Query:

Apart from deleting the CSV file you have another option to solve this problem in this case: filtering the folder so you only try to get data from the .xlsx files and no other file type. To do this, click on the step that is called “Source”. When you do this you’ll see that the step returns a table containing all the files in the folder you’re pointing at:

You’ll see that the table in this step contains a column called Extension which contains the file extension for each file. If you filter this table – which will insert a new step at this point in the query, which is ok – by clicking on the down arrow in the Extension column, delselecting the (Select All) option and selecting “.xlsx” so the table only contains .xlsx files then you can avoid this problem:

If, as in this example, the rogue file happens to be the first file in the folder and you’ve selected that first file to be your “sample” file when setting up the import, then you’ll also need to go to the query called Sample File in the Queries pane and make exactly the same change there (ie click on the Source step and filter to remove any non .xlsx files).

Understanding The “A Measure Is Used In Cross Highlighting” Error In Power BI

When interacting with a Power BI report you may occasionally get the following error:

Couldn’t load the data for this visual

We can’t display this visual because a measure is used in cross highlighting. Please remove the measure or cross highlight.

What does this mean? The most important thing to explain is that you have not done anything wrong: you have, unfortunately, run into a limitation of Power BI where it can’t generate the query needed for a selection on a visual. The issue occurs in some very rare cases when Power BI needs to filter a measure using multiple fields from different tables. Since only workarounds involve changing your visuals, changing the way you interact with your report or changing the way your report behaves, it is helpful to understand the scenarios where you will encounter it.

To do this, take the following basic Power BI dataset:

There are two products in the Product table – Apples and Oranges – and two countries in the Country table – UK and France, plus some sales values in the fact table. All the data in the dataset (minus the keys) can be shown in a table like so:

Note that fields from all three tables in the dataset (the Country field from the Country table, the Product field from the Product table and the Sales field from the Sales table) are used in this visual and Sales is used as a measure.

One way to trigger this error is to select any one of the rows in the body of this table, right-click and select Exclude:

The presence of a measure plus two fields from two different tables is the key to making the error occur: if you remove either the Country or Product fields then the Exclude will work ok. You could also remove the Sales measure, but it’s likely both of these changes will result in the visual not displaying what you want it to display so you’re better off educating your users to expect to see this error if they use Exclude. Selecting the rows you want to keep and using Include instead also works:

Another way to trigger the error is to cross-highlight this table by selecting two rows from different levels in a separate matrix visual on the same page. So, for example, let’s say the same data was displayed in a matrix visual alongside the table visual on the same page:

If, in the matrix visual, you select the France row and the UK/Apples row at the same time, then the error will occur again:

If you use Edit Interactions to stop the matrix from being able to cross highlight the table then you’ll be able to stop the error:

Another possibility is to select rows that are all at the same level to cross highlight instead of selecting rows from different levels. So, for example, if you change the second matrix to be another table and select the two rows for France and the row for UK/Apples, you won’t get the error:

I’m sure there are other scenarios where this occurs (I see this is called out by OKVIZ here for some of their custom visuals) but hopefully these examples are enough to help you understand what’s going on here.