Press "Enter" to skip to content

Category: Power BI

Solid Practices for Power BI

Published 2024-10-30 by Kevin Feasel

Paul Turley has the beginnings of a new series:

It’s time for a refresher and reboot on this important topic. Much has changed in the Power BI world, the core design principles remain the same, practices and architecture patterns have evolved over the past few years. Power BI has grown up in the enterprise space and Microsoft Fabric now adds new options and capabilities. Back in 2020, I began writing a series of blog posts titled “Doing Power BI the Right Way” and it has become my mission to evolve and maintain a current collection of the most important best practice recommendations. This has been my passion and topic of several conference talks, user group sessions and a book currently in development for O’Reilly that will help you prepare for the PL-300 Power BI Analyst exam and then guide you apply enterprise best practices in your solutions.

I work with hundreds of consulting clients who go through the same cycles, having the same experiences, facing the same challenges, many making the same mistakes, and many learning some of the same lessons. The purpose of this series is to share those lessons with you.

Click through for the overview, as well as an outline of what Paul will include in this series.

Comments closed

Power BI Automatic Aggregations and Databricks

Published 2024-10-29 by Kevin Feasel

Katie Cummiskey, et al, do a bit of caching:

Automatic aggregations streamline the process of improving BI query performance by maintaining an in-memory cache of aggregated data. This means that a substantial portion of report queries can be served directly from this in-memory cache instead of relying on the backend data sources. Power BI automatically builds these aggregations using AI based on your query patterns and then intelligently decides which queries can be served from the in-memory cache and which are routed to the data source through DirectQuery, resulting in faster visualizations and reduced load on the backend systems.

Click through to learn more about automatic aggregations, which SKUs of Power BI / Fabric are eligible, and how you can enable it for data coming from Databricks.

Comments closed

The Power of Pre-Attentive Attributes

Published 2024-10-24 by Kevin Feasel

Elena Drakulevska is seeing pink elephants:

In a world packed with data, how do you make sure your key points don’t get lost in the noise?

Enter the Pink Elephant Principle—a concept that makes sure your most important elements stand out, like a big pink elephant in the middle of a room. It’s impossible to ignore, and that’s exactly what you want for the critical parts of your report!

The irony of this is that the historical term of seeing pink elephants is a person so drunk that he’s hallucinating. Humor of the term aside, Elena drives home a very important principle around ensuring you take advantage of pre-attentive attributes to ensure users see what’s important with the least cognitive effort.

Comments closed

Value Filter Behavior in Power BI

Published 2024-10-23 by Kevin Feasel

Jeffrey Wang digs into a new feature:

The October 2024 Power BI update introduces an inconspicuous yet significant preview feature: Value Filter Behavior. This feature is activated by setting a new model-level property, ValueFilterBehavior, to Independent. The default setting of Automatic preserves the existing behavior, at least during the public preview period. This property controls how the DAX SUMMARIZECOLUMNS function behaves, which is central to most DAX queries generated by Power BI visuals.

Don’t just take my world for it — create any Power BI visual by adding columns, filters, and measures. If you are familiar with the Performance Analyzer or other tools that capture the DAX query issued by the visual, you will see something like this:

Read on for Jeffrey’s example and a dive into what’s going on.

Comments closed

Viewing Total Storage Consumption in Microsoft Fabric

Published 2024-10-23 by Kevin Feasel

Gilbert Quevauvilliers builds a report:

One of the things I have found when working with my customers in Microsoft Fabric is that there is currently no way to easily view the total storage for the entire tenant.

Not only that, but it would also be time consuming and quite a challenge to then find out what is consuming the storage. Could it be large files or tables or warehouse tables?

In this blog post I will show you how using a Notebook you can get details of the storage across your Microsoft Fabric Tenant.

Click through for an image of the Power BI report and how you can get there.

Comments closed

The Importance of Planning before Power BI Data Modeling

Published 2024-10-23 by Kevin Feasel

Kelly Broekstra recommends against jumping right in:

Who has been told by a manager or business person to just connect to the source data and start creating a new report? Here is my tip:

DON’T DO IT

All Power BI and Fabric reports must have a semantic model, which Microsoft describes as “a logical description of an analytical domain, with metrics, business-friendly terminology, and representation, to enable deeper analysis.” – Source

Read on to learn why and what you should instead do if you want to have a better long-term experience with Power BI.

Comments closed

Using Week-Based Calendars in Power BI

Published 2024-10-22 by Kevin Feasel

Marco Russo and Alberto Ferrari work in weeks:

Weekly calendars are common in manufacturing, retail, and any business that is sensitive to weekends or to the number of working days. For example, the scenario described in this article uses the number of pageviews on a website from 2019 to 2024, with data available until September 3, 2024. The website analyzed has a clear weekly trend, with slower traffic over the weekend, as shown in the following line chart with a daily granularity. It seems like a business website. A sports website would probably display the opposite trend.

Read on to see some of the challenges around week-based calendars. There’s a reason I have a “Dates and Numbers” category on Curated SQL and it’s exactly for things like this: some of the most common things we as humans work with are extremely complex and fraught with exceptions, including calendars.

Comments closed

Building an Impact Analysis Process

Published 2024-10-18 by Kevin Feasel

Marc Lelijveld needs more than the minimum impact analysis:

Imagine you have a semantic model in the Power BI Service (or Fabric if you will), and you’re about to make a breaking change to this semantic model. How do you inform your end users? How do you tell them about this change? In this blog I will zoom in to options you have in the interface that will help you to reach out to your users, looking at different aspects from other reports in Power BI, but also more complex the users that connect via Analyze in Excel.

Click through for the use case, why the built-in impact analysis option for Power BI isn’t sufficient, and what you can do to flesh it out.

Comments closed

Implementing Role-Playing Dimensions in Power BI

Published 2024-10-15 by Kevin Feasel

Teo Lachev puts on a mask:

Role-playing dimensions are a popular business requirement but yet challenging to implement in Power BI (and Tabular) due to a long-standing limitation that two tables can’t be joined multiple times with active relationships. Declarative relationships are both a blessing and a curse and, in this case, we are confronted with their limitations. Had Power BI allowed multiple relationships, the user must be prompted which path to take. Interestingly, a long time ago Microsoft considered a user interface for the prompting but dropped the idea for unknown reasons.

Given the existing technology limitations, you have two implementation choices for implementing subsequent role-playing dimensions: duplicating the dimension table (either in DW or semantic model) or denormalizing the dimension fields into the fact table. The following table presents pros and cons of each option:

Click through for that table, as well as some thoughts on viable approaches, including an edge case.

Comments closed

Tips for Optimizing Power BI Semantic Models

Published 2024-10-15 by Kevin Feasel

Koen Verbeeck shares some tips:

Power BI is designed to be user-friendly. With just a few clicks, you can import data from various sources, combine them together in one data model and start analyzing it using powerful data visualizations. This sometimes leads to a scenario where people are just importing data into the tool without giving it too much thought. When you’re working on a solo project on a small dataset, there probably won’t be too many issues. But what if your report is successful and you want to share it with your colleagues and maybe other departments? Or more data is loaded into the model, but refreshes are taking more and more time? Even other data sources are added into your model, but writing DAX formulas has become hard, and reports are slowing down.

In this article, we’ll cover a couple of tricks that will help you make your Power BI models smaller, faster and easier to maintain. In the immortal words of Daft Punk: “Harder. Better. Faster. Stronger”.

Click through for those tricks and tips.

Comments closed