Press "Enter" to skip to content

Category: Power BI

Text Search Performance Optimization in Power BI

Published 2023-03-06 by Kevin Feasel

Chris Webb provides advice about a relatively new feature:

In the blog post for the October 2022 release of Power BI Desktop there’s a brief description of a new optimisation in Power BI for filters that perform searches in text columns. In this blog post I’m going to share more details about this optimisation and how you can make sure your reports can benefit from it.

Read on to learn a bit more about how it works, as well as a few tips on ensuring that you’re able to take full advantage of this optimization.

Comments closed

Options to Export Power BI to Tables

Published 2023-03-01 by Kevin Feasel

Gilbert Quevauvilliers counts the ways:

I was recently helping out a customer and they contacted me asking why was the export option not in the format that they expected.

I had a look and now because there are so many options to export data, each one exports the data differently.

My goal for this blog post is to show you what each export type looks like, so when a user is exporting data, they can export in the format they expect.

It turns out that there are several such ways and Gilbert describes each.

Comments closed

Removing Diacritics with Power Query

Published 2023-02-28 by Kevin Feasel

Chris Webb gets rid of them scribbles what you sometimes find on perfectly good letters:

…then the output is “un garon trs g Nol”. As you can see, removing all the characters leads to unreadable text. Instead, what you have to do is find all the letters with diacritics (accents and other glyphs that can be added to characters) and remove the diacritics. Doing this may be ungrammatical and make it harder to understand the meaning of the text but in most cases the text will still be readable.

The bad news is that there is no straightforward way to do this in Power Query, and indeed there is no straightforward way to do this at all because there are no hard-and-fast rules about what to replace a letter with a diacritic with: should “ö” become “o” or “oe” for example? My first thought was to create a big lookup table with all the rules of what to replace each character with in, similar to the approach taken here for solving this problem in Google Sheets. Building a comprehensive lookup table would be gigantic task though.

Chris does take a different approach, though do read the comments because there are scenarios in which a simple removal of the diacritic can lead to a not-so-subtle alteration of the phrase.

Comments closed

Thoughts on the New Power BI Accessible Themes

Published 2023-02-27 by Kevin Feasel

Meagan Longoria is moderately pleased:

Everyone’s vision is a little different. It is rare (impossible?) that a color theme is accessible for everyone. For instance, while many people with color vision deficiency have trouble distinguishing red and green hues, others have trouble distinguishing blue hues. So when we optimize to accommodate one condition, we may make things more difficult for another condition. This happened with the change in accent color in Power BI Desktop from yellow to teal. Changing to teal increased color contrast, which was great for people with low vision, but it caused new issues for some people with color vision deficiency.

While I am very happy to see these new color themes, I hope everyone understands that they aren’t just generally accessible for all uses. As mentioned in the blog post, they specifically have better color contrast to achieve a contrast of at least 3:1, which is the contrast recommended by WCAG for non-text content.

Read the whole thing. There’s a delicate balancing act between having a complete color scheme and satisfying a variety of needs. It sounds like this theme doesn’t quite cut it, though hopefully there will be some improvements in the future.

Comments closed

Aggregations and Distinct Counts in Power BI

Published 2023-02-23 by Kevin Feasel

Phil Seamark doesn’t have time to wait for Power BI to count:

This article aims to show how you can speed up distinct count calculations in Power BI using the built-in user-defined aggregations feature. The user-defined aggregation feature in Power BI is designed to work with direct query models and usually gets used for calculations such as SUM, MIN, MAX etc. However, it can also work well for distinct count calculations using the pattern shown in this article.

It’s an interesting partial aggregation approach which works really well when the distinct count is a small percentage of the total.

Comments closed

Capabilities (and Limitations) of Power BI Migration Tools

Published 2023-02-20 by Kevin Feasel

Chris Webb talks tools:

I have so many customers wanting to migrate from legacy BI tools to Power BI. They are concerned that their current BI tool has an uncertain future. Licence renewals are looming and in the current economic climate organisations are looking to save money. Power BI is not only a lot cheaper than other BI tools, it’s a better tool overall and since Microsoft continues to make big investments in it then migration is clearly a no-brainer.

As a Power BI consultancy owner I have a problem though: I don’t have enough skilled people working for me to keep up with all this demand. What’s the answer? I know! Let’s build a tool that can help migrate all these legacy reports to Power BI!

The result is that, so far this year, I’ve seen or heard of five or six different Power BI migration tools built by various consultancies. That’s great and here at Microsoft we’re naturally supportive of our partners and want as many people to use Power BI as possible. I have reservations about some of these tools though, and these reservations fall into two categories.

Read on for Chris’s take on the topic.

Comments closed

Power BI Dataset Scale-Out

Published 2023-02-16 by Kevin Feasel

Teo Lachev digs into a preview feature:

Microsoft announced a public preview of Power BI Dataset scale-out (DSO) for Power Premium, Premium per User (PPU), and Power BI Embedded. In the comments below the announcement, the article implies that this feature is a replacement for the Azure Analysis Services scale-out. “If you have an AAS scale out and you migrate your databases (aka models aka datasets aka cubes) to Power BI Premium, you get scale out automatically and at no extra cost.” Scaling out for free? Sure, where do I sign?

But then further down the comments, we have this clarification “[Power BI DSO happens] if a dataset is on peak load and the vcores of your capacity aren’t maxed out. Keep in mind that scalability on a single instance isn’t linear. By scaling out, we can achieve a better utilization of available CPU resources for high workloads. On the other hand, if your vcores are already maxed out, then scaling out brings no further perf benefit.” Confused? So was I, and I reached for clarification to Microsoft. Below, is my best understanding of what happens behind the scenes.

Click through for the answer.

Comments closed

Working with Power BI Paginated Report Subscriptions

Published 2023-02-15 by Kevin Feasel

Olivier Van Steenland performs a conversion:

As a first step, I tried to “migrate” an example report from SQL Server Reporting Services to Power BI. In a previous blog post, I described the steps required to migrate successfully. You can find that blog post using the following link: Converting SSRS Reports to Power BI Paginated Reports.

In this blog post, I will look at the subscription functionality for Paginated Reports.

Read on for the process, as well as one limitation (and workaround).

Comments closed

Blank Rows and Limited Relationships in DAX

Published 2023-02-15 by Kevin Feasel

Marco Russo and Alberto Ferrari cover the blank row:

We dedicated a previous article to the blank row in DAX. In that article, the goal was to explain the differences between VALUES and DISTINCT. This article here focuses on how important it is to generate the blank row to guarantee that totals are always correct.

The blank row is created for regular relationships that are invalid – that is, when there is at least one row on the many-side that does not have a matching row on the one-side of the regular relationship. The same does not happen for limited relationships, which do not generate a blank row in similar conditions. Therefore, if a model contains a limited invalid relationship, developers must pay extra attention to how they create reports to avoid obtaining inaccurate results.

Read on for an example of what they mean.

Comments closed