Press "Enter" to skip to content

Category: Power BI

Performance Tuning Tables with Filters in Power BI

Published 2022-11-11 by Kevin Feasel

Chris Webb doesn’t want to wait:

There are four columns: Date, Town and two measures. One measure called [Fast Measure] is, as the name suggests, very quick to execute; the other measure, called [Slow Measure], is very complex and slow. The definitions are irrelevant here. Notice that there is a filter on this table visual so only the rows where [Fast Measure] is greater than 1 are shown.

If I measure the amount of time to render this table in Performance Analyzer, it takes around 17.5 seconds to run. However, if I remove the filter on [Fast Measure], the table only takes 8 seconds to run. Why? The filter is on the fast measure and surely more rows are returned without the filter, so wouldn’t the slow measure be evaluated more?

Click through for the answer.

Comments closed

Conditional Formatting from Text in Power BI

Published 2022-11-09 by Kevin Feasel

Mara Pereira shows us a trick:

Have you ever wondered if you can apply conditional formatting based on a text field/measure instead of a numeric field/measure?

If your answer is yes, then this trick is for you!

The other day I was working with a customer who asked something that I had no idea how to build.

They wanted to apply conditional formatting over some of their visuals, but they wanted the conditional formatting applied over a text field and not over a numeric field or a measure.

Read on to see how.

Comments closed

DirectQuery Partitions on Hybrid Tables

Published 2022-11-08 by Kevin Feasel

Shabnam Watson noticed something interesting:

While I was tracing some Power BI datasets with Hybrid tables, I noticed that that the DirectQuery partition of a Hybrid table is used in all queries submitted to the Hybrid table, whether or not they are asking for data from that Direct Query partition. On first look, this does not look good because Power BI should be able to use partition pruning for a partitioned table, meaning it should only scan certain partitions. For example, if you ask for historical data from 5 years ago, the current day’s data is not needed to answer that query and hence, the Direct Query partition for today should not be used in the query.

But do read on for more information about what’s going on, as it seems there are layers of nuance here.

Comments closed

Power BI Model Documenter Version 2.1.0

Published 2022-11-08 by Kevin Feasel

Marc Lelijveld has a new version of the Power BI Model Documenter:

It is long overdue, but time for a new updated version for the Power BI Model Documenter external tool! The latest release lasts from the first week of 2022 and since then close to 6000 people have downloaded the Model Documenter installer directly from the website, excluding the downloads and clones from the GitHub repository. I can’t share enough how happy I am with all the interactions I’ve had with people from all over the world providing feedback, asking questions and willing to contribute to the Model Documenter.

Read on to see what’s changed since the last release.

Comments closed

Profiler Errors against Power BI Dataset

Published 2022-11-07 by Kevin Feasel

Shabnam Watson troubleshoots an issue:

I was trying to trace a dataset I had published to Power BI service using SQL Server Profiler and I was getting this error:

Either the trace with the ID of ‘MicrosoftProfilerTrace1667261566’ does not exist in the server with the ID of ‘autopremiumhostnorthcentralus001-081’, or the user does not have permissions to access the object.

Read on for the solution.

Comments closed

The Importance of the Power BI Service

Published 2022-11-04 by Kevin Feasel

Reza Rad explains why the Power BI Service is useful:

The Power BI toolset comes in many shapes and forms. There is a Power BI Desktop, Power BI Mobile app, Power BI Report Server, and Power BI Service (and some other applications and components too). The questions I hear from the new users of Power BI are; Do I need to have an account for Power BI? do I need to use the Power BI website for creating visualization etc.? What is the Power BI website or service, and what is its usage? If I can do the reporting using Power BI Desktop for free, then why would I need the service? In this article and video, I will answer all of that.

Click through for a video or for the article explaining the purpose behind the Power BI Service. Having done work with places using Power BI Report Server and places using the Power BI Service, I will say that the latter takes more work to get corporate-compliant but offers a whole lot more.

Comments closed

Incorporating Power BI with Azure Synapse Analytics

Published 2022-11-04 by Kevin Feasel

Ginger Grant counts the ways:

The first is to connect Power BI to Azure Synapse to explore and visualize data. You can examine your datasets that you have loaded in your datalake with Power BI to help with the analysis of the data either for a data science solution or to determine how you are going to transform the data. For more information on how to do this, check out my previous blog .

Click through for three additional methods.

Comments closed

Refreshing Excel Data Model Tables via VBA

Published 2022-11-03 by Kevin Feasel

Chris Webb does some work in Excel:

Sometimes, when you’re analysing data, you need to be able to change variables and see what the impact is: for example you might want to see what your profit margin looks like if tax rates are set at different levels. Power BI’s what-if parameter feature can do this but it has the limitation that you can’t enter any value you like – you need to create a table containing all possible variable values in advance. The advantage the Excel Data Model/Power Pivot has over Power BI for this type of what-if analysis is that you have the Excel worksheet available, which is not only a place to display your report but which can also be used as a data source for tables in the Excel Data Model, making it easy for users to enter whatever variable they want. Up until recently, though, I assumed that if you were importing data from the worksheet into the Excel Data Model you would need to take some form of manual action, for example clicking a refresh button, to load the new data from the worksheet into the Excel Data Model when the data there changed. In this blog post I’ll show you how you can use VBA to solve this problem and build an elegant what-if analysis solution in Excel where no extra clicks are needed.

Read on for that demonstration.

Comments closed

Parallel Loading of Tables in Power BI Dataset Refresh

Published 2022-11-01 by Kevin Feasel

Chris Webb hits the turbo button:

Do you have a a large dataset in Power BI Premium or Premium Per User? Do you have more than six tables that take a significant amount of time to refresh? If so, you may be able to speed up the performance of your dataset’s refresh by increasing the number of tables that are refreshed in parallel, using a feature that was released in August 2022 but which you may have missed.

Click through for that tip.

Comments closed