Find Unused Objects In Power BI Semantic Models With Semantic Link Labs

A few weeks ago I wrote about how Semantic Link Labs now has tools with interactive UIs and showed how you could use this to view lineage and so Vertipaq-Analyzer-stuff in a notebook. I didn’t show what I think is the coolest new feature though: the ability to find the tables, columns and measures in a semantic model that aren’t used and which therefore could be deleted. There are tons of excellent third-party tools that do this available already of course but the advantage of using Semantic Link Labs for this is that you can automate the process of finding unused columns and run it from a notebook and, crucially, you have two ways of finding those unused columns: from analysing the structure of downstream reports and from analysing DAX queries captured in Workspace Monitoring.

To test this I used a simple semantic model with one table in a workspace with Workspace Monitoring enabled:

I then created a report with a single visual showing the Person Count measure broken down by gender:

Next I created a notebook with the following code that uses find_unused_objects:

%pip install semantic-link-labs
import sempy_labs as labs
labs.semantic_model.find_unused_objects(
    dataset = "insertsemanticmodelidhere",
    workspace = "insertworkspaceidhere",
    method = "WorkspaceMonitoring",
    visualize=True
)

This displayed the following widget which queried Workspace Monitoring and found three DAX queries in the previous seven hours:

Clicking the Analyze button showed me – as expected – that only the Gender and Person Count measures had been used in these three queries and that all the rest of the columns were unused:

Of course you don’t need to use the widget and can get a pandas dataframe back instead.

Find What Changed And When In Your Power BI Semantic Model

Here’s a situation I’ve found myself in many times. You’re called to help someone whose Power BI report is suddenly very slow. You ask whether any changes were made to the semantic model around the time performance got worse and the answer is no, absolutely not. You then spend anything from a few hours to a few days trying to fix the problem and when you find it, it turns out that someone did make a ‘minor’ change to the semantic model after all and this was the cause of it all. Wouldn’t it be good if you could see when things like measures were last changed? Well you can.

Many of the DAX INFO functions, which allow you to query semantic model metadata, have a ModifiedTime column which tells you the date and time that an object was last changed. So for example the INFO.MEASURES() (but not INFO.VIEW.MEASURES() ) function will tell you the date and time each measure in your semantic model was modified. You can query it like so from either the DAX query view in Desktop or the Service, or DAX Studio:

EVALUATE
SELECTCOLUMNS (
    INFO.MEASURES (),
    "Name", [Name],
    "Last Modified", [ModifiedTime],
    "Expression", [Expression]
)
ORDER BY [Last Modified] DESC

Here’s an example of the output of this query:

Of course if you’re troubleshooting a problem you will want to look at more than just the measures. I therefore put together this rather lengthy DAX query which looks at measures, calculated columns, calculation groups and lots of other things and gives you a list of all the things that have been modified, sorted in descending order by modification date:

DEFINE
    /* Set to NOW() - 7 to see only the last week of changes */
    VAR _ChangedSince = DATE(1900, 1, 1)
    /* Lookups reused across sections */
    VAR _Tables = SELECTCOLUMNS(INFO.TABLES(), "TableID", [ID], "TableName", [Name])
    VAR _Roles = SELECTCOLUMNS(INFO.ROLES(), "RoleID", [ID], "RoleName", [Name])
    VAR _Perspectives = SELECTCOLUMNS(INFO.PERSPECTIVES(), "PerspectiveID", [ID], "PerspectiveName", [Name])
    /* Joined by the definition's own ObjectID: older models have no FormatStringDefinitionID column */
    VAR _FormatStringDefs = SELECTCOLUMNS(INFO.FORMATSTRINGDEFINITIONS(), "OwnerID", [ObjectID], "ModifiedTime", [ModifiedTime], "Expression", [Expression])
    /* Detail rows run on drillthrough / show-as-table; joined by ObjectID like format strings */
    VAR _DetailRowsDefs = SELECTCOLUMNS(INFO.DETAILROWSDEFINITIONS(), "OwnerID", [ObjectID], "ModifiedTime", [ModifiedTime], "Expression", [Expression])
    /* Bi-directional relationships appear once per direction: keep one row per relationship */
    VAR _RelNames = GROUPBY(INFO.VIEW.RELATIONSHIPS(), [ID], "RelName", MINX(CURRENTGROUP(), [Relationship]))
    /* ---------- 1. Calculation logic: DAX that runs at query time ---------- */
    VAR _Calculations =
        UNION(
            SELECTCOLUMNS(
                NATURALINNERJOIN(_Tables, SELECTCOLUMNS(INFO.CALCULATIONGROUPS(), "TableID", [TableID], "ModifiedTime", [ModifiedTime])),
                "Object Type", "Calculation Group", "Name", [TableName], "Last Modified", [ModifiedTime], "Expression", ""
            ),
            SELECTCOLUMNS(INFO.CALCULATIONITEMS(),
                "Object Type", "Calculation Item", "Name", [Name], "Last Modified", [ModifiedTime], "Expression", [Expression]),
            SELECTCOLUMNS(INFO.MEASURES(),
                "Object Type", "Measure", "Name", [Name], "Last Modified", [ModifiedTime], "Expression", [Expression]),
            SELECTCOLUMNS(
                NATURALINNERJOIN(_FormatStringDefs, SELECTCOLUMNS(INFO.MEASURES(), "OwnerID", [ID], "Owner", "measure " & [Name])),
                "Object Type", "Dynamic Format String", "Name", "Format string of " & [Owner], "Last Modified", [ModifiedTime], "Expression", [Expression]
            ),
            SELECTCOLUMNS(
                NATURALINNERJOIN(_FormatStringDefs, SELECTCOLUMNS(INFO.CALCULATIONITEMS(), "OwnerID", [ID], "Owner", "calculation item " & [Name])),
                "Object Type", "Dynamic Format String", "Name", "Format string of " & [Owner], "Last Modified", [ModifiedTime], "Expression", [Expression]
            ),
            SELECTCOLUMNS(
                NATURALINNERJOIN(_DetailRowsDefs, SELECTCOLUMNS(INFO.MEASURES(), "OwnerID", [ID], "Owner", "measure " & [Name])),
                "Object Type", "Detail Rows Expression", "Name", "Detail rows of " & [Owner], "Last Modified", [ModifiedTime], "Expression", [Expression]
            ),
            SELECTCOLUMNS(
                NATURALINNERJOIN(_DetailRowsDefs, SELECTCOLUMNS(INFO.TABLES(), "OwnerID", [ID], "Owner", "table " & [Name])),
                "Object Type", "Detail Rows Expression", "Name", "Detail rows of " & [Owner], "Last Modified", [ModifiedTime], "Expression", [Expression]
            )
        )
    /* ---------- 2. Model structure: what the engine has to scan and join ---------- */
    VAR _Structure =
        UNION(
            SELECTCOLUMNS(INFO.MODEL(),
                "Object Type", "Model", "Name", [Name], "Last Modified", [ModifiedTime], "Expression", ""),
            SELECTCOLUMNS(
                NATURALINNERJOIN(
                    SELECTCOLUMNS(INFO.TABLES(), "ID", [ID], "ModifiedTime", [ModifiedTime]),
                    SELECTCOLUMNS(INFO.VIEW.TABLES(), "ID", [ID], "TableName", [Name], "Expression", [Expression])
                ),
                "Object Type", IF([Expression] = "", "Table", "Calculated Table"), "Name", [TableName], "Last Modified", [ModifiedTime], "Expression", [Expression]
            ),
            SELECTCOLUMNS(
                NATURALINNERJOIN(
                    SELECTCOLUMNS(INFO.COLUMNS(), "ID", [ID], "ModifiedTime", [ModifiedTime], "Expression", [Expression]),
                    SELECTCOLUMNS(FILTER(INFO.VIEW.COLUMNS(), [DataCategory] <> "RowNumber"), "ID", [ID], "ColumnName", "'" & [Table] & "'[" & [Name] & "]")
                ),
                "Object Type", IF([Expression] = "", "Column", "Calculated Column"), "Name", [ColumnName], "Last Modified", [ModifiedTime], "Expression", [Expression]
            ),
            SELECTCOLUMNS(
                NATURALINNERJOIN(
                    SELECTCOLUMNS(INFO.RELATIONSHIPS(), "ID", [ID], "ModifiedTime", [ModifiedTime]),
                    SELECTCOLUMNS(_RelNames, "ID", [ID], "RelName", [RelName])
                ),
                "Object Type", "Relationship", "Name", [RelName], "Last Modified", [ModifiedTime], "Expression", ""
            ),
            SELECTCOLUMNS(
                NATURALINNERJOIN(_Tables, SELECTCOLUMNS(INFO.HIERARCHIES(), "TableID", [TableID], "HierarchyName", [Name], "ModifiedTime", [ModifiedTime])),
                "Object Type", "Hierarchy", "Name", [TableName] & "[" & [HierarchyName] & "]", "Last Modified", [ModifiedTime], "Expression", ""
            )
        )
    /* ---------- 3. Data loading: M queries, partitions, refresh ---------- */
    VAR _DataLoading =
        UNION(
            SELECTCOLUMNS(
                NATURALINNERJOIN(_Tables, SELECTCOLUMNS(INFO.PARTITIONS(), "TableID", [TableID], "PartitionName", [Name], "ModifiedTime", [ModifiedTime], "QueryDefinition", [QueryDefinition])),
                "Object Type", "Partition", "Name", [TableName] & " / " & [PartitionName], "Last Modified", [ModifiedTime], "Expression", [QueryDefinition]
            ),
            SELECTCOLUMNS(INFO.EXPRESSIONS(),
                "Object Type", "M Expression / Parameter", "Name", [Name], "Last Modified", [ModifiedTime], "Expression", [Expression]),
            SELECTCOLUMNS(
                NATURALINNERJOIN(
                    SELECTCOLUMNS(INFO.TABLES(), "TableID", [ID], "TableName", [Name], "ModifiedTime", [ModifiedTime]),
                    SELECTCOLUMNS(INFO.REFRESHPOLICIES(), "TableID", [TableID], "SourceExpression", [SourceExpression])
                ),
                "Object Type", "Refresh Policy", "Name", [TableName], "Last Modified", [ModifiedTime], "Expression", [SourceExpression]
            )
        )
    /* ---------- 4. Security and perspectives ---------- */
    VAR _SecurityAndPerspectives =
        UNION(
            SELECTCOLUMNS(INFO.ROLES(),
                "Object Type", "Role", "Name", [Name], "Last Modified", [ModifiedTime], "Expression", ""),
            SELECTCOLUMNS(
                NATURALINNERJOIN(_Tables, NATURALINNERJOIN(_Roles, SELECTCOLUMNS(INFO.TABLEPERMISSIONS(), "RoleID", [RoleID], "TableID", [TableID], "ModifiedTime", [ModifiedTime], "FilterExpression", [FilterExpression]))),
                "Object Type", "RLS Filter", "Name", [RoleName] & " / " & [TableName], "Last Modified", [ModifiedTime], "Expression", [FilterExpression]
            ),
            SELECTCOLUMNS(INFO.PERSPECTIVES(),
                "Object Type", "Perspective", "Name", [Name], "Last Modified", [ModifiedTime], "Expression", ""),
            SELECTCOLUMNS(
                NATURALINNERJOIN(_Tables, NATURALINNERJOIN(_Perspectives, SELECTCOLUMNS(INFO.PERSPECTIVETABLES(), "PerspectiveID", [PerspectiveID], "TableID", [TableID], "ModifiedTime", [ModifiedTime]))),
                "Object Type", "Perspective Table", "Name", [PerspectiveName] & " / " & [TableName], "Last Modified", [ModifiedTime], "Expression", ""
            )
        )
    /* ---------- 5. OPTIONAL: newer compatibility levels only ----------
       UDFs and hybrid-table data coverage definitions don't exist on older models, and referencing
       them fails the WHOLE query at compile time with "references an object or property that is
       unavailable in the current edition of the server". If you see that error, comment out this
       VAR and remove _NewerFeatures from the final UNION below. */
    VAR _NewerFeatures =
        UNION(
            SELECTCOLUMNS(INFO.USERDEFINEDFUNCTIONS(),
                "Object Type", "UDF", "Name", [Name], "Last Modified", [ModifiedTime], "Expression", [Expression]),
            SELECTCOLUMNS(
                NATURALINNERJOIN(
                    SELECTCOLUMNS(INFO.PARTITIONS(), "PartitionID", [ID], "PartitionName", [Name]),
                    SELECTCOLUMNS(INFO.DATACOVERAGEDEFINITIONS(), "PartitionID", [PartitionID], "ModifiedTime", [ModifiedTime], "Expression", [Expression])
                ),
                "Object Type", "Data Coverage Definition", "Name", [PartitionName], "Last Modified", [ModifiedTime], "Expression", [Expression]
            )
        )
EVALUATE
FILTER(
    UNION(_Calculations, _Structure, _DataLoading, _SecurityAndPerspectives
    , _NewerFeatures
    ),
    [Last Modified] > _ChangedSince
)
ORDER BY [Last Modified] DESC

The part of the query that checks for changes in newer features, such as DAX UDFs, may error on older models with lower compatibility levels so you may need to comment that, and the part of the UNION that references it, out. Instructions on how to do this are in the comments in the query.

It’s also worth pointing out that sometimes the modification dates change when you don’t expect them to. For example I saw that when I added a calculation group to my semantic model all the modification dates of my measures were changed too, probably because adding a semantic model in Desktop changes the discourage implicit measures property on the model automatically. But overall it seems to behave sensibly enough for it to be useful so give it a try and let me know what you think. I can also imagine it being useful to combine this information with the list of measures, columns etc that are touched by a given DAX query (something I wrote about here) so you can see whether any objects that the query uses have changed recently.

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.