Secondly, and most importantly, there’s a new custom property tab for Power Query datasets:

As well as showing the M code used for the dataset (at the top) and a list of any M parameters (at the bottom), you can now see the ID of the Cloud Connection used by the dataset in the middle of the pane, copy it, and then press the manage button to go to the “Manage Connections and Gateways” pane in the Fabric portal, paste the ID into the search box and find the Cloud Connection used by the dataset, so you can edit it or refresh the credentials if you need to:

As a Power Query fan I have always been a big fan of its integration in Report Builder and this makes it much easier to use.

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.

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.

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.

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.

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.

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!

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.

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!

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.