Choosing A Power BI Storage Mode For Reporting On Data From Azure Databricks

In case you haven’t already seen the blog post on the Power BI blog or the discussion on LinkedIn, we at Microsoft published a new white paper last week to help you decide which storage mode to use when you’re using Power BI to create semantic models and reports on data stored in Azure Databricks. You can find the announcement and the link to the paper here.

While the paper was commissioned by Microsoft it was written by an independent expert, Liping Huang, who has extensive expertise with Power BI and Databricks and who has worked for both Microsoft and Databricks in the past. Liping is someone I have a huge amount of professional respect for and I think she did a great job on a very tricky project. I think the end result is very honest and fair. I’d also like to call out Eiki Sui from my team (if you can read Japanese check out his blog – if you don’t then browser translation tools work well) for providing the initial inspiration and managing the project on the Microsoft side.

So what does the white paper say? Which storage mode is best? Unsurprisingly, it depends and you need to read the whole thing. In order to keep the scope manageable the paper doesn’t test every single possible storage mode scenario and it also focuses on performance and excludes cost and governance considerations. Direct Lake on OneLake comes out on top in the majority of tests but a Composite Model with a DirectQuery fact table, Dual mode dimension tables and Import mode aggregations is best for larger data volumes, similar to what I described in this blog post. Of course your data, semantic models and reports will be different so the main aim of the paper is to help you run your own tests to decide what works best for you.

Referencing Images And Other Files In Power BI Reports Using OneLake URLs

Several years ago I wrote a very popular blog post about how to store images for your reports inside your Power BI semantic model. It solved the problem of how you could use display images (for example of products) inside your reports without making those images available via a public URL or personal OneDrive Embed Codes. I was very proud of how efficient the M code to do this was but the code was complicated and storing images as text inside a semantic model makes refreshes a lot slower and increases the size of your semantic model in memory, so it’s not ideal. The good news is that, if you have enabled Fabric in your tenant, the August 2026 release of Power BI brings a much better way of solving this problem: you can now store your images (and indeed other files) inside OneLake and reference them from there. This means you can store your images in a secure location, alongside all of your other data, and make them available for use in Power BI. What’s more this doesn’t just work for images, it also works for other types of files such as GeoJSON files used by map visuals.

To illustrate how this works, I created a semantic model containing data from the 2024 UK general election. The details of the model don’t matter that much except for the fact that there was a column called Country Name containing the distinct values England, Scotland, Wales and Northern Ireland, and a measure called Total Electorate. I published the model to a workspace and then, in the same workspace, created a lakehouse. In the Files section of the lakehouse I created a new folder and then uploaded five jpg files of the flags of England, Scotland, Wales and Northern Ireland as well as the Union Jack.

I made a note of IDs of the workspace and lakehouse. Then, in the semantic model, I created a measure with the following definition which returned the url of the image in my lakehouse for the selected country or, if no country was selected, returned the url of the Union Jack:

Flag =
IF (
    HASONEVALUE ( 'Election'[Country name] ),
    "https://onelake.dfs.fabric.microsoft.com/insertworkspaceidhere/insertlakehouseidhere/Files/Flags/"
        & SELECTEDVALUE ( 'Election'[Country name] ) & ".jpg",
    "https://onelake.dfs.fabric.microsoft.com/insertworkspaceidhere/insertlakehouseidhere/Files/Flags/United Kingdom.jpg"
)

I then set the Data Category property of this measure to be Image URL:

Finally I create a report with a table visual in, dragged in the Country Name column, the Flag measure and the Total Electorate measure, and saw the flag images displayed in the table:

I then uploaded a GeoJSON file containing the outlines of the countries of the United Kingdom from the Office for National Statistics Open Geography Portal to another folder called GeoJSON in my lakehouse:

I viewed the Properties for the file and copied its URL:

I then created a Shape Map visual, dragged the Country Name and Electorate measures into it, went to the Map Settings properties and set the Type to “URL” and pasted in the URL of the GeoJSON file from OneLake into the Enter A URL box. This gave me a Shape Map showing the outline of the UK and colouring representing the size of the electorate:

[Note: at the time of writing this doesn’t work with the Azure Maps visual but this will be fixed soon]

Nothing here in terms of displaying images or using GeoJSON files with maps in Power BI reports is new. What is new, as I said, is where the images and the GeoJSON files are stored. The ability to store these files in OneLake means no more workarounds are needed to work with dynamic images as in the flag example, and it also means that you never need to upload a GeoJSON file containing sensitive data used by a map visual – you just need to store the GeoJSON file in OneLake and if you need to change it you simply replace the file without having to edit the report. And of course you can now automate the loading of these files into OneLake in the same way that you automate the loading of other types of data. If you’re a Power BI shop that hasn’t enabled Fabric yet then this is one more reason to do so.

Tools With Interactive UIs In Fabric Notebooks With Semantic Link Labs

There’s so much going on in the Fabric community that it can be hard to keep up with it all. Semantic Link Labs is a great example: in the six months or so since I last had a proper look at it my colleague Michael Kovalsky has done a whole load of cool things and it wasn’t until I had a chat with him recently that I realised how much had changed. Most importantly, for someone old-fashioned like me who still likes tools with a UI, a lot of new functionality has been added which has a UI and is usable with minimal coding.

To illustrate this, I created a new Fabric notebook in a workspace and installed Semantic Link Labs:

%pip install semantic-link-labs

I then headed over to the Semantic Link Labs Code Examples and copied some of the code from there into cells in my notebook. For example, the following code:

import sempy_labs.semantic_model
sempy_labs.semantic_model.lineage_view()

…opened up a tool for exploring report and semantic model lineage appearing within the notebook. I could connect to a semantic model:

…and then see which reports are connected to it and even look for broken visuals in those reports:

There’s also a version of Vertipaq Analyzer:

import sempy_labs as labs
dataset = 'insert id or name of semantic model here'
workspace = 'insert id or name of workspace here'
x = labs.vertipaq_analyzer(dataset=dataset, workspace=workspace)

…and a whole load of other things which probably deserve their own blog post. So if, like me, you had assumed that Semantic Link Labs was for people who like writing code rather than using a UI, take another look – you’ll probably find something useful.

Displaying The Output Of Detail Rows Expressions In Power BI Reports Using The Paginated Report Visual

In last week’s post I mentioned that while Power BI reports (unlike Excel PivotTables) do not support the Detail Rows Expression feature, it is possible to partially work around this limitation by using the paginated report visual. In this post I’ll show you how I was able to do this and what is and isn’t possible.

Starting with the same semantic model I used for last week’s post, already published, the first major task was to create a paginated report to show the data returned by a measure’s Detail Rows Expression. I opened Power BI Report Builder, created a new paginated report and created a connection back to that semantic model. Next I created two datasets (in the paginated report sense of the term) called Customer and Product that listed all the values from the Customer column of the Customer table and the Product column of the Product table. Here are the two DAX queries I used:

EVALUATE VALUES('Customer'[Customer])
EVALUATE VALUES('Product'[Product])

I then created two paginated report parameters, also called Customer and Product, which were bound to these two datasets and which were set to allow multiple values. Here’s what the Customer parameter looked like:

The Product parameter was set up the same way but bound to the Product dataset.

I then created a third dataset called Drillthrough. To get the columns for the dataset I first bound it to this DAX query:

EVALUATE
DETAILROWS([Sales Amount])

Next I set up two query parameters on this dataset, also called Customer and Product, and bound them to the two report parameters created above:

I then changed the query for the dataset to the following so it could reference the two query parameters:

EVALUATE
CALCULATETABLE(
	DETAILROWS([Sales Amount]),
	RSCustomDaxFilter(@Customer,EqualToCondition,[Customer].[Customer],String),
	RSCustomDaxFilter(@Product,EqualToCondition,[Product].[Product],String)
)

Side note: if you’re wondering what the RSCustomDaxFilter() function is, it’s not a real DAX function but it’s a feature of paginated reports that allows you to handle multi-value parameters in DAX queries relatively easily. I blogged about it back in 2019 here and John Kerski has a good post on it here. It looks like the feature has been improved since 2019 in that the above query gets translated to DAX that looks something like this:

EVALUATE
CALCULATETABLE (
    DETAILROWS ( [Sales Amount] ),
    FILTER (
        VALUES ( 'Customer'[Customer] ),
        ( 'Customer'[Customer] IN { "Chris", "Helen" } )
    ),
    FILTER (
        VALUES ( 'Product'[Product] ),
        ( 'Product'[Product] IN { "Apples", "Oranges" } )
    )
)

The DAX generated now uses the IN operator rather than the multiple OR conditions that it used to, which is an improvement.

Finally I put a table on the report to display the output of the Drillthrough dataset. Here’s what the report looked like in design mode:

And here’s what it looked like when I ran it:

I then published the paginated report.

The second major task was to use this paginated report in a Power BI report. I opened Power BI Desktop, created a live connection to my published semantic model, then created a page with a simple matrix visual on it showing the Sales Amount measure broken down by Customer and Product:

I then created a second page and configured it as a drillthrough page and configured it to accept drillthroughs on Customer and Product:

I then added a Paginated Report visual to the page, dragged the Customer and Product columns into the Parameters pane:

And then linked the visual up to the published paginated report and mapped the paginated report’s parameters to the parameters in the visual’s parameters pane:

Finished! With all that done I could right-click on a cell in the matrix visual and drill through to the paginated report visual on the other page:

There is, however, one big problem with this approach: it is hard-coded to work with just one measure. If you have multiple measures in your source visual then you have no way of knowing which measure a user has clicked on – so there’s no way you could pass that measure’s name to the paginated report. This is a limitation of the drill through feature in Power BI; the only workaround I can think of would be to either use calculation groups or the measures table technique (as described here) instead of multiple measures so that you can capture the selection of the “measure” as the selection on a column.

I still hope that one day that workarounds like this won’t be necessary and that Power BI reports support drill to detail in the same way that Excel PivotTables do. If you agree, please leave a comment!

Using Detail Rows Expressions To Drill To A Different Fact Table In Power BI

If you have a DirectQuery fact table in Power BI you can use user-defined aggregations to improve query performance; querying a smaller, summarised copy of your data in an Import mode aggregation table is always going to be faster than querying a large fact table containing all your detail data that is in DirectQuery mode. What’s more a composite model like this can have a much smaller footprint in memory than a model where all your tables are in Import or Direct Lake mode, which means you can use a smaller Fabric capacity SKU. However, in some cases you can take the same tables that you would use to create a composite model like this and solve the same problem slightly differently without using aggregations.

To illustrate, consider the following semantic model:

There are two dimension tables in Dual mode, a totally hidden fact table called SalesDetail in DirectQuery mode which contains transaction-level data:

…and a fact table called SalesSummary in Import mode which contains an aggregated copy of the same data:

There is one visible measure called Sales Amount which sums up data from the Sales column on the SalesSummary table:

Sales Amount = SUM(SalesSummary[Sales])

At this point, an end user would only be able to query data from the SalesSummary table – which would be nice and fast because they are querying the aggregated data. But since the SalesDetail table is hidden there’s no way an end user can query it (or at least query it easily because remember, folks, hiding an object is not security). So how can they get at that detailed, transaction-level data that all end users love?

The answer is through the use of Detail Rows Expressions, the finest feature in the whole of Power BI that nobody knows about. Marco has a great article on it here that I suggest you read but basically it allows you to configure a DAX table expression associated with a measure that is usually used to show all the rows that contribute to the value that the measure displays. I’m going to use it that way here but the important point about it is that you can use it to return a table expression from anywhere in your semantic model – not just the table where your measure gets its data.

So for example, I can set the Detail Rows Expression on the Sales Amount measure to this:

SELECTCOLUMNS (
    'SalesDetail',
    "Order ID", 'SalesDetail'[OrderID],
    "Product", 'SalesDetail'[Product],
    "Customer", 'SalesDetail'[Customer],
    "Sales Amount", 'SalesDetail'[Sales]
)

Even though the Sales Amount measure sums up data from the Sales column on the SalesSummary table, when an end user clicks Show Details on the Sales Amount measure in an Excel PivotTable:

…they can get the detail rows from the SalesDetail table showing all the order data for the cell they clicked on:

Thus the Detail Rows Expression allows you to “drill to detail” from the SalesSummary table to the hidden SalesDetail table. This works because the Customer and Product dimension tables are related to both SalesSummary and SalesDetail, so whatever selection has been applied to SalesSummary is also be applied to SalesDetail.

What are the advantages of doing this instead of configuring SalesSummary as an aggregation table? Because data from the SalesDetail table is only available via the Detail Rows Expression feature then it gives you as a semantic model developer a lot more control over how end users access that detail fact data: you can make sure they get the rows they need in the most efficient way because you have total control over the DAX used and you can also prevent them from dumping out large amounts of data by writing controls on how much data can be returned. For example you could write some logic in your Detail Rows Expression that ensures data is only returned if a single date is selected. With this approach users would not be able to drag all the Order IDs into a PivotTable (potentially running a very expensive query) because they could not see the Order ID column to do so.

There are plenty of disadvantages to this technique too though, compared to building aggregations. First and foremost you can only make use of Detail Rows Expressions in Excel PivotTables; I wish we supported them natively in Power BI reports but we don’t. It is possible, however, to use the Paginated Report Visual in a Power BI report to partially work around this limitation and I’ll show you how in my next post. Also, while this allows you to have one Import mode table with aggregated data and one DirectQuery fact table, with aggregations you can have multiple Import mode aggregation tables for a single DirectQuery fact table which can result in even better performance. And of course it might be that you actually want your end users to see and access all the data in your DirectQuery fact table and query it however they want.

This is in fact an old technique I remember from the days of Analysis Services Multidimensional, but it has somehow been forgotten. I wanted to blog about it, though, because as DirectQuery and composite models become more and more important I think it deserves to be used more.

Analysing Power BI Data Using Copilot In Excel

This week there was another big Copilot-related announcement for Power BI – but on the Excel blog. You can now analyse data from Power BI semantic models using Copilot in Excel. Full details on how to enable this are in the docs here, but what does it look like?

Using the same semantic model from last week’s blog post on doing ABC analysis in the M365 Copilot app, I opened Excel, opened the Copilot pane and entered the same prompt:

Use this semantic model <insert URL of semantic model here> to do an ABC analysis. 
The upper boundary for the first group is £290000 and the upper boundary for the second group is £790000. 
Filter the transactions to just 20th January 2025.

…and fairly soon after saw two worksheets created. One had a few thousand rows from the fact table in my semantic model:

…and the other had the ABC Analysis I asked for:

Interestingly, unlike in my blog post last week, Copilot did not use the DAX UDF in the semantic model to do the ABC analysis. Instead it did what most Excel users would prefer and grabbed the raw data, did some of the analysis in the DAX query that got that raw data, then did most of the analysis using Excel formulas. It got the answer I was expecting though and the logic was consistent with the logic in my DAX UDF.

The data on the transactions worksheet was a static copy and I would love it if, in future, Copilot created Excel tables linked to a DAX query and a connection back to the semantic model so that when the data in the model changed then the workbook would update automatically.

Next, just to see if it worked, I used the brandkit skill (also announced this week) to make it look more corporate with the following prompt:

 @brandkit make this look like a Microsoft branded report

Luckily there was already a Microsoft brand kit available to me; the result was a report which (I guess) was a bit more Microsoft-blue and which had a Microsoft logo in the top right-hand corner:

Job done, report created.

So what? The functionality exists and it works well, which is good to know. But there’s a more interesting question to consider here: who cares? Is anyone going to use this? After all, “AI helps you analyse data” is not exactly headline news anymore. But because Excel Copilot:

  • Works well enough – which this seems to do
  • Works well enough before other competing tools which also work well enough become commonplace on corporate desktops
  • Produces analysis in a way – Excel formulas – that is more accessible and understandable to more people than, say, Python code
  • Does this analysis directly inside the tool that most people already know and love, Excel, without needing anything extra installed and not in some other tool – even if that tool can generate Excel workbooks

…then I think there’s a very good chance that this will be popular. Probably more popular as a way of analysing data from Power BI semantic models than the M365 Copilot app, and maybe even more popular than Power BI reports as a way of analysing data from Power BI semantic models.It’s Export to Excel for the age of AI!

Power BI, M365 Copilot And The Importance Of DAX UDFs

This was a big week for Power BI Copilot with the announcement of a new direction for the feature. If you haven’t done so already, read the two announcement blog posts here and here and check out this demo video. There are some pros and cons to this change (yes, you’re going to need an M365 Copilot licence to use it but no Fabric capacity is needed any more – this works with models in Pro workspaces too) and there are more features still to come and problems to be solved, but the bottom line is that all of the work you’ve done preparing your semantic models for Copilot is not wasted and while the existing Power BI Copilot worked pretty well, the combination of M365 Copilot and Power BI semantic models works much, much better.

Probably the biggest change is that whereas the existing Power BI Copilot can answer questions in a number of different ways, for example by constructing a Power BI visual, M365 Copilot usually just generates and runs DAX queries against the underlying semantic model. It’s very good at generating syntactically correct DAX queries and of course your model’s AI Instructions are respected, but two risks remain:

  1. M365 Copilot generates a DAX query that returns the correct result but not necessarily one that is optimal in terms of performance or CU cost
  2. Multiple end users want to do the same kind of analysis but don’t know precisely how it should be performed, give vague instructions or are inconsistent, so they might each get slightly different results

Both of these problems can be solved in different ways but as a semantic model developer I find that adding DAX User-Defined Functions (UDFs) to your model is the easiest way to do so. This is something I wrote about last year but I thought I would revisit the topic because of its importance in the new world of M365 Copilot. If you’re going to centralise all your business logic in a semantic model then that should include the definition of complex analytical operations, something that a DAX UDF that returns a table can handle better than a DAX fragment embedded in your AI Instructions or a text description of how your analysis should be performed.

Using the same semantic model containing UK Land Registry Price Paid data and the same ABC analysis UDF from that previous post, I ran a similar test to the one in that post in M365 Copilot. I only made one change to the AI Instructions for the semantic model, adding two new paragraphs highlighted below:

This semantic model contains a DAX user-defined function called ABC that does an ABC analysis on the data in the Transactions table. It takes three parameters defined as follows:
AUpperBoundary - an integer value which is the upper boundary of transactions in the A group
BUpperBoundary - an integer value which is the upper boundary of transactions in the B group
AnalysisDate: a datetime value which is the date to filter transactions on
The function returns a table which can be used in an EVALUATE statement in a DAX query.

If a user asks for an ABC analysis then you must **always** use this function. Do not add any other columns or totals or try to do any other calculations on what this function returns, just return the table that the function returns and nothing else.

If the user does not specify the values needed for the function parameters please ask the user for them - do not make any assumptions. Never try to generate the DAX for an ABC analysis yourself because it may result in incorrect or inconsistent results.

For example if I wanted to see the number of transactions which took place on 1st January 2025 divided into three groups:
A - transactions between £0 up to and including £250000
B - transactions above £250000 up to and including £700000
C - transactions above £700000
I could call the function as follows:
ABC(250000, 700000, DATE(2025,1,1))

I added the first new paragraph because I found M365 Copilot was trying to be too clever and adding extra columns and subtotals that I didn’t think were necessary, and running extra DAX queries in order to do so. I added the second one when I found that if I asked a vague question then Copilot made too many assumptions and didn’t use my UDF.

The following prompt:

Use this semantic model <insert URL of semantic model here> to do an ABC analysis. The upper boundary for the first group is £290000 and the upper boundary for the second group is £790000. Filter the transactions to just 20th January 2025.

…resulted in the following output:

…which is exactly what I wanted. What’s more it worked consistently across all my tests and I could see that the only DAX queries run looked like this:

EVALUATE
ABC(290000, 790000, DATE(2025,1,20))
ORDER BY [ABC Group]

When I tried a vaguer prompt, like:

Use this semantic model <insert URL of semantic model here> to do an ABC analysis.

M365 Copilot asked me for the extra information it needed to do the ABC analysis:

Interestingly when I deleted the AI Instructions above and pasted more or less the same text into the Description property of the UDF, I found that M365 Copilot did not use the UDF and instead it generated its own DAX query – which still gave the correct result but which included a number of additional measures from the semantic model that I didn’t think were relevant. So explaining in your AI Instructions what UDFs are present and what they should be used for is necessary.

Anyway, to reiterate my point, I think DAX UDFs are going to be more and more important in the future. Up to now they’ve been useful as a way of encapsulating and reusing logic across calculations inside a semantic model; Power BI, alas, doesn’t provide a way for a report to show the output of a table-valued UDF. As more users consume data from Power BI semantic models outside Power BI reports, in tools like M365 Copilot that can answer questions by generating any DAX query they want, then DAX UDFs provide a way for semantic model developers to specify how certain types of analysis should be performed to ensure consistency and performance.

Using Fabric Operations Agents And Workspace Monitoring With Power BI

This week, in the announcement about support for Fabric Pipelines in Workspace Monitoring, I noticed that it came with an Operations Agent that actively monitors and analyses Pipeline activity. And that got me thinking, since Workspace Monitoring also contains Power BI activity data, why not create an Operations Agent to actively monitor Power BI too?

I decided to test a really simple scenario. I created a semantic model that contained several measures, one of which returned an error. I then created a report from that semantic model with two pages: one with several working visuals and one with a visual that used the broken measure and which therefore returned an error.

I published this report to a workspace with Workspace Monitoring enabled.

I then created an Operations Agent in the same workspace and connected it to the KQL Database associated with Workspace Monitoring:

Here are my Agent Instructions:

*** Operational Instructions ***
1. Alert me when a DAX query on any of the Power BI semantic models in this workspace returns an error, for example when the DAX in a measure, except when the error is the result of a query being cancelled
2. When you alert me, give me the following information:
a) The username of the user that generated the error
b) The date and time of the error
c) The error message
d) The IDs of the semantic model, the report and the visual that generated the error
e) The OperationID of the query
*** Semantic Instructions ***
1. The SemanticModelLogs table contains information about Power BI activity
2. Every time there is an error, there will be an event with the OperationName "Error"
3. The EventText column for these Error events contains the error message
4. Errors generated by query cancellations, which can be ignored, have an error message in the EventText column that starts with the text "The operation was cancelled by the user"
5. The Timestamp column contains the date and time of the events
6. The ExecutingUser column contains the username of the user that generated the error
7. The ApplicationContext column contains a JSON fragment which gives the IDs of the semantic model (DatasetId), report (ReportId inside the Sources array) and visual (VisualId inside the Sources array)
8. The OperationID column contains the OperationID

Here’s the Playbook that the Operations Agent generated:

And here’s the KQL generated for the DAX Query Error Event Alert:

// DaxQueryErrorEvent: one row per non-cancelled DAX query error event from SemanticModelLogs
declare query_parameters(startTime:datetime, endTime:datetime);
let DaxQueryErrorFacts =
  ['SemanticModelLogs']
  // Restrict to DAX query error events and exclude user-cancelled operations
  | where ['Timestamp'] between (startTime .. endTime) and ['OperationName'] == "Error" and not(tostring(['EventText']) startswith 'The operation was cancelled by the user')
  // Parse ApplicationContext once for downstream extraction
  // Parse ApplicationContext once for downstream extraction
  | extend ApplicationContextParsed = todynamic(['ApplicationContext'])
  // Extract DatasetId directly from ApplicationContext
  | extend DatasetId = tostring(ApplicationContextParsed.DatasetId)
  // Extract first report/visual source, if present, from ApplicationContext.Sources[]
  | extend Sources = todynamic(ApplicationContextParsed.Sources)
  | extend FirstSource = iif(isnull(Sources) or array_length(Sources) == 0, dynamic(null), Sources[0])
  | extend ReportId = tostring(FirstSource.ReportId)
  | extend VisualId = tostring(FirstSource.VisualId);
let DaxQueryErrorEvent =
  DaxQueryErrorFacts
  | project
      // Entity identity and timestamp
      Id = ['OperationId'],
      Timestamp = ['Timestamp'],
      // Value requests
      DaxQueryErrorIsNonCancelledError = true, // by construction: filtered to non-cancelled errors
      ExecutingUser = ['ExecutingUser'],
      ErrorTimestamp = ['Timestamp'],
      ErrorMessage = ['EventText'],
      DatasetId = DatasetId,
      ReportId = ReportId,
      VisualId = VisualId,
      OperationId = ['OperationId'];
DaxQueryErrorEvent

I have to admit it took several iterations to get the Agent Instructions right so that working KQL was generated, and I would not have been successful without testing the KQL myself and understanding why it wasn’t working. It was not particularly hard to do this though.

I then saved and started the Operations Agent.

Next I opened the report that contained the broken visual, so that I saw an error in the report, and waited. After a few minutes I got the following message in Teams:

So I got a Teams notification when a report containing a broken visual was rendered, which is great and proves the point that Operations Agents can be used to monitor Power BI when used with Workspace Monitoring. You’ll notice that the Teams message doesn’t include the error message, the semantic model, report or visual IDs or any of the other information I requested (although the KQL query generated by the Playbook does return that information) but I guess I need to spend more time tuning the Agent Instructions. Other things I can imagine doing with an Operations Agent include monitoring for failed semantic model refreshes and slow DAX queries. Definitely something I need to spend more time investigating.

The Benefits Of Using Direct Lake Mode In Power BI

This is a blog post I’ve been meaning to write for a long time. Since Fabric launched there has been a lot of focus on Direct Lake mode in Power BI and a lot of people used it because it was the cool new thing. Arguably, we at Microsoft have been guilty of telling people to use it because it was the cool new thing without properly explaining what the benefits are of using it. Direct Lake doesn’t completely replace other storage modes: in a recent post I talked about when Import/DirectQuery composite models are the best choice; Marco wrote a good article on Direct Lake vs Import mode which makes the case for the continuing relevance of Import mode for many scenarios. So what are the main benefits of using Direct Lake mode? Here are the ones I think are important:

Faster and cheaper “refresh”

Refreshing an Import mode semantic model can be slow and can consume a lot of CUs on your capacity. Refreshing a Direct Lake semantic model (also known as framing) is almost instant and consumes minimal CUs. So Direct Lake is better, right? Well no, it’s more complicated than that. Even if refresh is cheap in Direct Lake mode you still need to get the data into OneLake in order to use it, and even if you have data in an existing lakehouse you may need to create a copy to apply additional transformations or to apply VOrder or other table-level optimisations, and it’s the cost and performance of this creation of a gold layer of tables that you should compare with Import mode refresh.

The good news is that it is often faster and cheaper to use another Fabric engine, like Spark, to load the data you need into your gold layer in OneLake than to do an Import mode refresh. For example, in this post on reddit my colleague David Browne gives a simple example of how loading a 20 million row fact table in Spark is half the cost in terms of CUs and marginally faster than refreshing the same table in Import mode; this other post on reddit has similar findings. Other BI projects I’ve seen have shown significant improvements in the time and cost of the operations needed before your end users can view reports with the latest data. Of course there are a lot of factors involved here (How frequently do you need to refresh? Are you using incremental refresh? Are you partitioning your fact table and increasing the amount of parallelism during a refresh? etc) and it is true that, at least at the time of writing, Direct Lake may be marginally slower to query than Import and will have different CU cost characteristics when queried. But the evidence is strong enough for me to say that you should do some testing to see whether Direct Lake will be faster and cheaper for your project.

There are other aspects of cost that need to be taken into account. For example, this “shifting left” of development effort from semantic model refresh to loading data into OneLake means that a specialised set of Power BI semantic model skills around Import mode refresh can be replaced by more commodity skills in areas such as Spark. You may also be able to eliminate a layer in your architecture by using Direct Lake: I’ve seen cases where a relational database is put on top of a data lake just to serve data for Import mode refreshes, something that isn’t necessary with Direct Lake.

My friends who work on internal Microsoft BI teams (who are, incidentally, some of the most sophisticated Fabric developers that I know) tell me that they prefer Direct Lake over Import because it makes them a lot more productive, again reducing costs. One reason is because it means that if they discover a bug in their semantic model that means they have to refresh, they can have their semantic model ready to be queried in seconds once the bug is fixed. They can also do things like run unit tests on feature branches before merging into their repo by spinning up a test semantic model in seconds, querying it then tearing it down again.

Avoidance of memory limits and timeouts during refresh

When you’re working with large Import mode semantic models it’s fairly common to run into errors when you hit memory limits or timeouts during refresh. This can be frustrating. These limits don’t exist when you’re loading data into OneLake for consumption by a Direct Lake model, and while there are plenty of other things that can go wrong when loading data depending on the Fabric workload you’re using, in general Import mode refreshes are harder to manage and require more specialised knowledge.

Reuse of data by multiple semantic models

Think how many Import mode semantic models there are in your tenant. Think how many copies of the same dimension tables, like the Date or Product or Customer dimension tables, there are across them. There may even be cases where the same fact table is duplicated across multiple semantic models. All of these duplicate Import mode tables need to be refreshed individually, which takes time and costs CUs, and refreshing them at different times may result in different semantic models containing different data.

On the other hand, if you plan ahead and use shortcuts judiciously, you can load all of your fact and dimension tables into OneLake once and use them in as many Direct Lake semantic models as you want. This can greatly reduce the CU cost and the time it takes before your data is ready to be queried by a report; it also means that all the semantic models contain exactly the same data. I haven’t seen many cases where people have taken advantage of this yet – probably because it does require you to plan ahead – but where it does happen it leads to huge efficiency gains. And with OneLake security you can apply security once and have it enforced consistently across all Direct Lake semantic models that use the same tables.

In the future it seems likely we’ll be moving away from large, complex semantic models that contain all the data an end user might possibly need to smaller, more focused models that work better for AI. At the same time, while a traditional Power BI report can only connect to one semantic model, the new Rayfin/Fabric apps can connect to multiple semantic models. This means it’s even more likely that the same tables will need to be present in several different semantic models.

Reuse of data by different Fabric engines

Finally, building on that last point, when all of your data is in OneLake with OneLake security applied, it’s not only available to be consumed via Direct Lake semantic models but also by any of the other Fabric engines: it can be queried in SQL via the SQL Endpoint, analysed using code in notebooks and so on. While it is possible to write the contents of an Import mode model to OneLake using the OneLake integration feature, it makes a lot more sense just to load the data into OneLake and use Direct Lake instead if you care about this.

Summary

Maybe the one thing I would disagree with Marco on in the article I referenced above is his statement that Direct Lake is only useful for the 2-3% of semantic models that are hard to manage in Import mode – those that are above 200-400GB in size. While I don’t think you should rip and replace any existing Import mode models that are currently working well, and while very large models will need DirectQuery fact tables, I think the reasons I’ve listed above mean that any new enterprise-scale project that is built completely on Fabric should at least consider using Direct Lake. I’m sure there are people out there who feel differently though, so let’s have a discussion in the comments!

[Thanks to David Browne, Justin Martin, Tamas Polner and Christian Wade for their help writing this post]

Understanding The “Database Was Evicted To Balance The CPU Load” Error In Power BI

A few months ago I wrote about a rare error – the “Maximum allowable memory allocation” error – that may occur when the physical machine, or node, that a semantic model is running on in the Power BI Service comes under memory pressure. Recently, someone I was working with who was doing some load testing showed me a related error:

The operation was canceled and the database was evicted to balance the CPU load on the node. Please try again later.

This error is so rare that a web search returns no results, but since at least one other colleague at Microsoft has seen it I thought it would be good to blog about it.

Put simply, this error is the CPU version of the “Maximum allowable memory allocation” error in that when CPU usage on a node gets too high then a semantic model (maybe the one that is causing the high CPU usage, maybe not, and indeed maybe there is no single culprit) is picked to be moved to a different, quieter node. When this happens any queries or refreshes that are running on the selected semantic model are cancelled and this error is returned.

It’s easy to see how load testing can trigger this error, since load testing involves running a lot of concurrent DAX queries and therefore generates high CPU usage on the semantic model. As with the “Maximum allowable memory allocation” error, if you see this error once you may just be unlucky but if you see it repeatedly in normal production use you are probably the cause, in which case enabling semantic model scale out may help but the real solution is to tune your semantic model to reduce CPU usage. How to do that is out of the scope of this blog but as with so many things in Power BI you should start by looking at the DAX in any calculated columns (if you’re getting the error during a refresh) or in your measures (if you’re getting the error when users query the semantic model), and by looking at how your data is modelled.