A Fine Slice Of SQL Server
Koen Verbeeck has a few more mistakes we shouldn’t make:
What advice would you give to someone new to Power BI? What are common Power BI mistakes to avoid? In this article, we will discuss several Power BI mistakes to avoid to help you navigate this powerful tool effectively.
Click through for thoughts on DAX, governance, security, and more.
Leave a CommentMarco Russo and Alberto Ferrari get us to 80%:
The Pareto analysis is an analytical technique used to identify the most impactful elements within a dataset, based on the principle that a small proportion of causes often leads to a large proportion of effects. For example, the ABC Classification in DAX Patterns is also based on the Pareto principle. However, typical implementations often face limitations. The analysis based on the Pareto principle commonly uses categorical axes, such as customer names or identifiers, making it impossible to leverage a continuous axis on a Power BI line chart. A categorical axis creates a scrollbar on the line chart when there are too many data points, limiting the ability to compare the distribution of data points in different categories within the same line chart.
This article shows how to overcome this limitation by introducing a numeric axis reflecting each item’s position based on the selected measure.
Read on to see how it all works.
Leave a CommentThe series of blog posts I wrote last year on semantic model memory usage, in particular this post on the query memory limit and the “This query uses more memory than the configured limit” error in Power BI, gets a lot of traffic. Since writing that post on the query memory limit I’ve written a few follow-ups on common mistakes that lead to increased query memory usage, such as this one on measures that never return a blank. Today’s post is sort of in that series but it isn’t about a design mistake – it’s just to point out that distinct count measures can be surprisingly memory-hungry.
Read on for Chris’s findings and the explanation, as well as a couple of potential workarounds if you find yourself in this situation.
Leave a CommentMarco Russo and Alberto Ferrari show that tracking weekdays is like an onion:
Computing time intelligence calculations in DAX is rather simple. However, as soon as the requirements are not trivial, the complexity of formulas skyrockets, and it is necessary to have a very good understanding of several details about DAX to obtain a good formula. In this article, we show a simple requirement: the need to maintain a filter on weekdays while computing time intelligence. As you are about to read, it will require several complex steps despite being a simple requirement; but let us start by clarifying what we want to obtain and what a filter-preserving column is.
Click through for the full article.
Comments closedMarco Russo summarizes the announcements:
I usually do not write about announcements and new features until we have had time to try and test them in the real world. However, there are always exceptions, and some of the announcements at the Microsoft Fabric Conference 2025 fall into this category because I have worked with them enough to provide hands-on feedback.
In short, these are the topics I am covering in this blog post:
- Direct Lake and Import mode
- Calendars in DAX
- User-Defined Functions (UDF) in DAX
These weren’t the headline-grabbers of the conference, but Marco explains the importance behind each of them.
Comments closedEugene Meidinger blows the doors off of this conspiracy:
For years, I told people to avoid iterators. I compared them to cursors in SQL, which are really bad, or for loops in C# which are normally fine. I knew that DAX was column based and that it often broke down when doing row-based operations, but I couldn’t tell you why.
Advice to avoid iterators is often based on a misunderstanding and a misapprehension of how the Vertipaq engine works. If you are blindly giving this advice out, like I was, you are promoting a fundamental misunderstanding of how DAX works.
Eugene has since opened his eyes and can see through the lies.
Comments closedHamza Boubou builds a dynamic report:
There was a requirement from my client that seemed simple at first but turned out to be a Trojan horse after deeper investigation. The goal was to create a Power BI Time Comparisons Top N Analysis page, giving users complete control over the Top/Bottom N products based on multiple metrics. Users needed to define the period, compare it with other periods, and adjust the N parameter dynamically.
Read on to see how Hamza was able to solve this customer request.
Comments closedMarco Russo and Alberto Ferrari inform us that we owe them money:
The Accounts Receivable (AR) Aging report helps companies track overdue receivables and better manage their cash flow. It is a common requirement from the finance department that often represents a challenge for Power BI reports, especially when you want to show the trend over time. Here is a typical visualization for the Open Amount at the end of each month, colored by the age range of the Accounts Receivable.
Read on for a dive into the report concept and some of the most common issues you may run into.
Comments closedThe recent announcement of Surge Protection gives Fabric/Power BI capacity admins a way to restrict the impact of background operations on a capacity, preventing them from causing throttling. However, at the time of writing, Surge Protection does not prevent users that are running expensive DAX or MDX queries – which are interactive operations – from causing problems on your capacity. Indeed, right now, there is no direct way to stop runaway queries from consuming a lot of CUs, although there is something you can do which will help a lot: reducing the query timeout.
Read on for information about why Surge Protection doesn’t currently work with DAX and MDX queries, and how you can change the query timeout. This is kind of interesting considering that, outside of the Microsoft Fabric world, we typically move the query timeout higher rather than lower, to deal with long-running queries.
Comments closedMarco Russo and Alberto Ferrari disambiguate a pair of operators:
Have you ever wondered what the subtle difference between ALL and ALLCROSSFILTERED might be? The family of ALL functions and modifiers includes some common functions, like ALL and ALLSELECTED, and some fancier and less frequently-used functions, like ALLNOBLANKROW and ALLCROSSFILTERED. This article discusses what ALLCROSSFILTERED is, why it is there in DAX, and when and how developers should use it.
Read on for that answer, along with several helpful demos.
Comments closed