The Problem With Approximate Distinct Counts In Power BI – And How To Solve It

As you’ve probably seen in the blog post for the September 2026 release of Power BI, Import mode and Direct Lake mode semantic models now support the ApproximateDistinctCount() DAX function. I tested its performance on a Direct Lake semantic model with a 1.4 billion row fact table based on the NYC Taxi sample data and as you would expect, it was a lot faster than doing a regular distinct count in most cases. For example when I asked for the distinct count of values from the Total Amount column by Date, my test query took around 6 seconds, consumed around 82 seconds of CPU time and 206,294 KB of memory:

In comparison, the same query getting an approximate distinct count of values from the Total Amount column only took around 4 seconds and around 48 seconds of CPU time, although interestingly rather more memory – 1,063,476KB.

That’s a pretty good improvement given that distinct count measures are often the cause of slow, expensive (in CU terms) DAX queries.

However, there is one big problem with using ApproximateDistinctCount() in your measures: your end users might not like it. If you go to your boss and say to them “Guess what! Your report is now twice as fast and the only downside is that new approximate distinct counts are only about 1.6% away from the actual values” they will very likely get upset and reply that their numbers have to be totally, 100% correct or they won’t be able to make the right decision. Which of course is probably rubbish because in most cases approximate distinct counts are good enough to make decisions. For example I took the same queries that I was testing above and turned them into two line charts which were indistinguishable to the naked eye:

Are you going to win that argument with your boss though? No.

Does this mean that approximate distinct counts are useless except for the rare scenarios where your end users are sophisticated enough to accept them? Well maybe there is a way to solve this problem and find a compromise. If you create a field parameter that allows end users to switch between seeing approximate distinct counts and distinct counts (making approximate distinct counts the default selection) in your reports then you can at least tell your end users that they can use a slicer to choose whether they see fast but slightly inaccurate results or slower but accurate results:

They can then browse quickly and look for trends using the approximate distinct counts and then, when and if they need to see totally accurate values, they can switch to the distinct count values.

Improvements To Power Query Integration In Power BI Report Builder

There are some nice improvements to Power Query integration in the latest version of Power BI Report Builder that, while they are documented, didn’t get an official blog post but which I think deserve to be highlighted. First of all the “Get Data” button has been renamed “Power Query”:

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.

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!