Category: DAX

Monitoring The Impact Of Full-Text Indexing On Semantic Model Refresh In Power BI

The September release of Power BI saw the introduction of a powerful new feature: full text indexing, which makes searching and filtering on text columns much faster and enables the use of the new TextContains() and TextSimilarity() DAX functions. You need to enable it on a per-column basis and when you do that you have the option to rebuild the full-text index for those columns when you do a Full refresh or when you do a refresh of the type Indexes. So the big question is: how long does it take to build a full-text index on a column and what impact will it have on your overall refresh times?

Obviously the answer is “it depends” and you’ll have to test. So, to test it myself, I added a full-text index to a column containing street names (so therefore fairly short strings) in a semantic model containing data from my favourite public data source, the UK Land Registry Price Paid data. The Street column contained 73,206 distinct values and the table itself contained 160,257 rows. While refreshing the index I ran a Profiler trace to see what I could see and I spotted a pair of Progress Report Begin/End events with the subclass Tabular Object Processing that represented the index build:

Here’s what the TextData column for the Progress Report End event contained:

Finished building persisted full-text index for column ‘Street’ of table ‘Transactions’. (TableTMID=’14’, ColumnTMID=’28’)

For this particular refresh the index build took 1392ms (from the Duration column) and used 484ms of CPU Time (from the CPUTime column). So fairly quick, but I can imagine that on a column containing longer strings and with more distinct values then refreshing the index could take a significant amount of time. The docs specifically point out that significantly slower refresh times could result from adding full-text indexes to too many text columns.

Is it worth enabling full-text indexes right now? They can’t be used by Power BI reports yet, but as the last few weeks have showed, in the future there are going to be more and more front-ends of different types that can run any arbitary DAX against your semantic model beyond regular Power BI reports. Full text indexing opens up new types of analysis which, traditionally, Power BI semantic models were not good at and which are going to be more common in “chat with your data” scenarios: for example, imagine a semantic model containing hotel booking information and a text feedback column – you’re only going to be able to answer questions like “how many refunds did we give to customers who complained about air conditioning?” with a DAX query that uses the TextSimilarity() function. So, to answer my own question, yes I think it is worth exploring this feature and checking what impact it will have on your refresh times.

ApproximateDistinctCount In Power BI Is Sometimes Slower Than DistinctCount

As a follow-on from last week’s post about the new ApproximateDistinctCount function in DAX, I want to share something I found out from my recent testing for this function: ApproximateDistinctCount is sometimes slower than DistinctCount. This sounds counter-intuitive, right? After all ApproximateDistinctCount is a performance optimisation for the sometimes slow, expensive DistinctCount function. But it turns out that there are certain engine optimisations for DistinctCount that can’t be replicated for ApproximateDistinctCount which mean that for low-cardinality columns using DistinctCount can be faster.

Last week’s post showed an example of where ApproximateDistinctCount wins so I won’t bother showing another one. Consider, though, the following, very basic Direct Lake semantic model consisting of just the 2017 data from the New York Taxi dataset:

The table itself has only got 11.7 million rows and the VendorId column on it only contains two distinct values. I built two measures in this model:

Distinct Vendor ID = 
DISTINCTCOUNT('green_tripdata_2017'[VendorID])
Approx Distinct Vendor ID = 
APPROXIMATEDISTINCTCOUNT('green_tripdata_2017'[VendorID])

Running a DAX query that returns the Distinct Count version of the measure for a lot (85562) of rows on a cold cache gives the following performance:

As you can see, the query itself takes an impressive 188ms and the single scan uses just 110ms of CPU time. The memory usage for this query was only 8028KB.

Now look at the same query but for the Approximate Distinct Count measure:

It’s still fast but it now takes 688ms and the scan uses 547ms of CPU time. Memory usage was also higher 410441KB – the current implementation prioritises performance over memory overhead. These numbers are small and the lesson is an old one: test first before you make any changes in production and only think about making changes if you’re sure DistinctCount is actually causing problems. Performance may change in the future too: one optimisation rolled out this week already that drastically improved the performance of ApproximateDistinctCount in my tests.

The next question you have will probably be this: if ApproximateDistinctCount is slower on low-cardinality columns, how low is “low”? After all you probably aren’t going to be doing a distinct count on a column with only two distinct values in it or running many DAX queries that return 86000 rows. Guess what: the answer is that it depends and again you’ll have to test. I saw worse performance for ApproximateDistinctCount on a column with around 11000 distinct values in it but your mileage may vary.

Does this mean ApproximateDistinctCount is useless? Not at all! If you have a very large semantic model and you’re trying to do a distinct counts on a column with millions of distinct values then it can be a lifesaver. Just don’t use it indiscriminately.

[Thanks to Bismarck Hsu for the information in this post]

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.

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.

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!

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.

New Book: “Extreme DAX” 2nd Edition

“Extreme DAX” by Michiel Rozema, Madzy Stikkelorum and Henk Vlootman

Continuing my series of not-reviews of the latest Power BI-related books (and there are fewer and fewer of them being published) that I receive free copies of, here’s the second edition of a book that for some reason I missed first time around: “Extreme DAX”. The first few chapters cover familiar territory: data modelling, calculated columns, measures, time intelligence calculations etc. Of course there are several other books available that explain these concepts with the canonical text being “The Definitive Guide To DAX”; “Extreme DAX” does a good job with the basics and includes the latest additions to the language such as UDFs but I can’t help but think it would have been better to skip all this and assume that the reader knows it all already. Luckily things get a lot more extreme as the book progresses and it shifts to showing how to solve complex – but nevertheless real-life – Power BI problems with DAX. There’s a whole chapter on Auto-Exist for example which is pretty hardcore. For me this is where the real value of the book lies and there’s enough good content here for any advanced Power BI developer to want to buy it. Definitely worth checking out.

Power BI Semantic Model Refresh Warnings

Since March 2026, Power BI semantic models have started showing warnings in their Refresh History in the Service. This has scared a few people but in fact all that is happening is that errors which were there all along and which don’t prevent refreshes from completing are now being flagged. Documentation on this feature can be found here but let’s see an example of the type of errors that can cause these warnings.

Consider the following semantic model that consists of a calculated table called Table With Error and a physical table called Sales with two physical columns called Product and Sales, two calculated columns called Sales Forecast and VAT Forecast, and two measures called Sales Amount and Tax Amount.

Here are the definitions of the calculated columns:

Sales Forecast = 'Sales'[Sales] * 1.1
VAT Forecast = 'Sales'[VAT] * 1.1

Here are the definitions of the measures:

Sales Amount = SUM('Sales'[Sales])
Tax Amount = SUM(Sales[Tax])

And here is the the definition of the calculated table:

Table With Error = 
FILTER(
'TableThatDoesNotExist', 
'TableThatDoesNotExist'[ColumnThatDoesNotExist]>1
)

There are some problems here: the VAT Forecast calculated column, the Tax Amount measure and the Table With Error calculated table all return errors because they refer to tables or columns that do not exist. You can see these errors in Power BI Desktop easily, for example in the Data pane where these items have warning triangles next to them:

…or if you look at their definitions:

None of these errors stop you from refreshing or publishing but of course you can’t use any of these items in your reports.

If you do publish and refresh this semantic model via the UI (although this does not happen if you refresh via the XMLA Endpoint) you’ll see the message “Refresh completed with warnings”:

If you click the Show link in the Details column and then the Show link in the yellow box that appears, you’ll see a dialog showing the errors for all the broken items:

If you see warnings like this you should probably go and either fix the items that are causing them or delete them. Errors like this happen frequently when you delete items in your semantic model that have measures, calculated columns or calculated tables that depend on them; there are plenty of other similar scenarios that will cause errors too.

Power BI, Parallelism And Dependencies Between SQL Queries In DirectQuery Mode

This is going to sound strange, but one of the things I like about tuning Power BI DirectQuery semantic models is that their generally-slower performance and the fact you can see the SQL queries that are generated to get data makes it much easier to understand some of the innermost workings of the Power BI engine. For example this week I was trying to tune a DAX query on a DirectQuery model using DAX Studio and the Server Timings showed me something like this:

As I described here, Power BI can send SQL queries in parallel in DirectQuery mode and you can see from the Timeline column there is some parallelism happening here – the last two SQL queries generated by the DAX query run at the same time – but everything has to wait for that first SQL query to complete. Why? Can this be tuned?

Here’s the scenario that produced the query above. I have a DirectQuery semantic model built from the ContosoDW SQL Server sample database:

There are three base measures defined:

Distinct Customers = DISTINCTCOUNT(FactOnlineSales[CustomerKey])
January Customers = 
CALCULATE([Distinct Customers], 
KEEPFILTERS('DimDate'[CalendarMonthLabel]="January"))
Monday Customers = 
CALCULATE([Distinct Customers], 
KEEPFILTERS('DimDate'[CalendarDayOfWeekLabel]="Monday"))

Note that these measures are written specifically to prevent fusion from taking place: each measure generates a separate SQL query. Here’s what DAX Studio’s Server Timings shows for the DAX query generated for the table shown above:

As you can see, the three SQL queries generated by the DAX query are run in parallel.

Now consider the following measure:

IF Test = IF([Distinct Customers]>3000, [January Customers], [Monday Customers])

Here’s what this measure returns:

If you run the query generated for this visual in DAX Studio, Server Timings shows what I showed in the first screenshot in this post:

The last two substantial SQL queries, on lines 4 and 5, can only run when the first SQL query, on line 1, has finished. The details of SQL queries tell you more about what’s going on here. The first SQL query, on line 1, just gets the values for the [Distinct Customers] measure for all rows in the table:

The WHERE clauses for the SQL queries on line 4:

..and line 5:

…show that these last two queries only get the values for the [January Customers] and [Monday Customers] measures for the rows where the [IF Test] measure needs to display them. And this explains why the first SQL query has to finish before these last two SQL queries can be run: the WHERE clauses of these last two SQL queries are constructed using the results returned by the first SQL query.

There is another way of evaluating the IF condition in the [IF Test] measure. Instead of “strict” evaluation, where the engine only gets the value of [January Customers] for the rows in the table where [Distinct Customers] is greater than 3000 and only gets the value [Monday Customers] for the remaining rows, it can get values for [January Customers] and [Monday Customers] for all rows in the table and then throw away the values it doesn’t need. This is “eager” evaluation and as you would expect, Marco and Alberto have a great article explaining strict and eager evaluation here that is worth reading; Power BI can decide to use either strict or eager evaluation with the IF function depending on which one it thinks will be more efficient. However you can force the use of eager evaluation by using the IF.EAGER DAX function instead of IF:

IF EAGER Test = 
IF.EAGER([Distinct Customers]>3000, [January Customers], [Monday Customers])

Here’s what Server Timings shows for the DAX query that uses IF.EAGER:

As you can see, the use of IF.EAGER means that the three substantial SQL queries generated by Power BI for this DAX query can now be run in parallel because there are no dependencies between them: they get the values of [Distinct Customers], [January Customers] and [Monday Customers] for all rows in the table. However, even though these three SQL queries are now run in parallel, it doesn’t result in any performance benefits here because it looks like the three queries are slower as a result of all being run at the same time. Power BI has made the right call to use strict evaluation with the IF function in this case but if you see it using strict evaluation I think it’s worth experimenting with IF.EAGER to see if it performs better – especially in DirectQuery mode where Power BI knows less about the performance characteristics of the database you’re using as your data source.

[Thanks to Phil Seamark for helping me understand this behaviour]

New Books: “The Definitive Guide To DAX” 3rd Edition And “Microsoft Power BI Visual Calculations”

For some reason I haven’t had any free copies of books to review recently; maybe the market for tech books has finally collapsed with AI? Books are still being published though and luckily, as someone who once published a book via an O’Reilly imprint, I have a lifetime subscription to O’Reilly online learning which gives me free access to all the tech books I ever need. Two books were published in the last few months that I was curious to read: the third edition of “The Definitive Guide To DAX” by my friends Marco Russo and Alberto Ferrari, and “Microsoft Power BI Visual Calculations” by my colleague Jeroen ter Heerdt, Madzy Stikkelorum and Marc Lelijveld. As I’ve said many times, I don’t write book reviews here (least of of reviews of books by friends or colleagues where I could never be unbiased), but I think there’s some value sharing my thoughts on these books.

“The Definitive Guide To DAX”, 3rd Edition

It’s generally accepted that the one book that anyone who is serious about Power BI should own is “The Definitive Guide To DAX”. If you don’t already own a copy you should buy one, but since most people who read my blog probably have one already the more interesting question to ask is what’s new in the third edition and whether it’s worth upgrading – especially since I’d seen Marco say that the book had been completely rewritten. I’ve heard the “completely rewritten” line before and I was sceptical but it turns out that it really is a very different book. It’s not completely rewritten because there is material there from previous editions but there are a lot of changes.

First of all, as you would expect, all the new additions to DAX since the second edition was published are covered including user defined functions, visual calculations, calendar-based time intelligence functionality and window functions. These are all really important features you will want to use in your semantic models and reports so this is the main reason you’d want to buy a copy of this edition.

Secondly, the main (and justfied) criticism of the previous editions was that they were, as we say in the UK, “heavy going”. They had absolutely all the information you would ever need but they were not the easiest books to read or understand. That has been addressed in the third edition: the tone is a little bit more friendly and difficult concepts are now explained visually as well as in text. As a result it’s easier to recommend the book for beginners.

Thirdly, some advanced topics (for example around performance tuning) have been dropped. For example I searched for the term “callback” in this new edition and found no mentions; that’s not true of the second edition. I have mixed feelings about this because it means the book isn’t as “definitive” as it used to be, but I can understand why it’s happened: with so much new content to add, keeping these advanced topics would have made an already long book too long. And let’s be honest, how often do you look at the details of a DAX query plan? If the aim is to teach DAX then cutting content means it’s easier for the reader to focus on the core concepts.

In summary, then, another great piece of work from Marco and Alberto and worth buying even if you have a copy of an earlier edition.

“Microsoft Power BI Visual Calculations”

A whole book about visual calculations? As I mentioned above, they’re covered in one chapter of “The Definitive Guide To DAX” but that book focuses on DAX; this one takes more time to explain the concepts and, crucially, includes a lot of practical examples of how to use them. Like user-defined functions, when visual calculations were released there was an explosion of community content showing how they can be used to solve problems that were difficult to solve in Power BI before – problems that no-one could have been anticipated that would be solved with visual calculations. The real value of this book is showing how to build a bump chart or a tornado chart with visual calculations and that makes it worth checking out.

Closing thoughts: why buy a book?

As you would expect, a lot of the information contained in these books is already available for free somewhere on the internet. And with AI you don’t even need to know how to search for it or stitch it all together – you can ask a question and get an answer customised to your exact scenario. So why buy books any more? I guess it depends on whether you only want to get your problems solved or understand how to solve problems yourself. For me (even though my attention span has eroded in recent years, just like everyone else’s) the only way to grasp really difficult concepts is through long-form written explanations or training courses, not fragments found in blog posts or 10-minute videos. I suspect that AI is the final nail in the coffin of the tech publishing industry but the tech book industry not being viable any more is not the same thing as tech books not being useful any more. Or maybe I’m just old-fashioned.