Press "Enter" to skip to content

Category: Power BI

Rating a Dashboard

Published 2024-08-22 by Kevin Feasel

Martin Schoombee explains the challenge of dashboard review:

A little while ago I was asked to rate a Power BI dashboard. The person who asked, participated in a Power BI challenge (I’ll call it that because that’s the way they are being marketed) and wanted some feedback on the submission. I agreed on the condition that the feedback would be public and in the form of a blog post.

Martin has a thoughtful explanation of the difficulty of providing a review (especially without important context around what the end users intend to do) but then does yeoman’s work talking about the visuals.

Comments closed

Troubleshooting Non-Editable Power Query Parameters in Microsoft Fabric

Published 2024-08-20 by Kevin Feasel

Soheil Bakhshi digs into a problem:

Power Query is a powerful tool within the Microsoft Fabric environment, enabling users to manage data sources and transform data efficiently. However, a common issue you may face is that after publishing the Semantic Model, the Power Query parameters either do not appear or are greyed out, making them non-editable. In this post and its accompanying YouTube video, I’ll walk you through the steps to diagnose and fix these problems, ensuring that your parameters work as expected in your published semantic models.

Click through for the video and a pair of common reasons.

Comments closed

Finding Columns in Memory in Power BI Direct Lake Mode

Published 2024-08-20 by Kevin Feasel

Chris Webb goes searching:

As you probably know, in Power BI Direct Lake mode column data is only loaded into memory when it is needed by a query. I gave a few examples of this – and how to monitor it using DMVs – in this blog post from last year. But which columns are loaded into memory in which circumstances? I was thinking about this recently and realised I didn’t know for sure, so I decided to do some tests. Some of the results were obvious, some were a surprise.

Read on for the answer.

Comments closed

Power BI Data Type Optimization

Published 2024-08-20 by Kevin Feasel

Nikola Ilic shows how important it can be to choose the right data types:

For demo purposes, I’ll be using a fact table that contains the data about chats performed by a customer support department of the fictitious company Customer First. This table includes approximately 9 million rows, which is not considered a large table in the context of Power BI and analytical workloads. For the sake of simplicity, let’s pretend that our model consists of only this single table. Finally, a semantic model is configured as an Import mode model. If you want to learn how your data is stored in Power BI, I suggest you start by reading this article first.

Data was loaded into Power BI from the underlying data source (SQL Server database) as-is, without any additional optimizations applied.

Nikola walks through the process of finding the most expensive columns in terms of data size and using the least precise acceptable value. One other thing that I commonly see is identity columns or other keys on fact tables. Those are very rarely necessary, because the point of a fact table is typically to aggregate it in some fashion. And these keys are unique (by design), meaning they won’t compress very well and will take up a lot of space. Looking at Nikola’s example, my next question would be, knowing that the name of the table is factChat, does chatID tie to some chat dimension? If not, is it actually necessary for reporting? Again, if not, that could shave off another 60 MB or so from the data model.

Comments closed

Highlighting a Single Data Point in Power BI

Published 2024-08-13 by Kevin Feasel

Kurt Buhler points something out:

Effective visualizations provide context so that you can interpret the numbers and what they mean to you. Is this number bad or is it good? This is particularly important for visuals that aim to provide a quick, 3-second overview, like cards, KPIs, and simple trendlines. You can provide context by comparing to a target, but if no target is available, you can also compare to a measure of central tendency, like the average or median. However, instead of comparing to an aggregate, you might also want to compare to other categories.

Consider the following example, which shows the desired end result for this article: a plot which highlights a selected value so that the user can compare it to all others. This example uses some DAX and formatting with a line chart and scatterplot to achieve the result of a joint plot atop a jitter plot. If you want to learn more about what a joint plot or a jitter plot is, we gave an overview of these and similar chart types in a previous article.

This is something I find frustratingly difficult with Power BI. Kurt does a great job of showing how to get there, but it seems like it should be a lot easier to do.

Comments closed

Storing Images in Kusto and Visualizing in Power BI or Data Explorer

Published 2024-08-12 by Kevin Feasel

Hauke Mallow shares what is probably a bad idea:

Kusto is a fast and scalable database designed to ingest, store, and analyze large volumes of structured and semi-structured data. For non-structured data like images, Azure Storage is typically the best choice. Databases can reference image data on storage via a URL, meaning images are not directly stored in Kusto. However, there are scenarios where storing image data in Kusto is beneficial. In this blog post, we will explore when it makes sense to store images in Kusto, how to store them, and how to visualize this data using Azure Data Explorer dashboards or Power BI.

I suppose the main benefit would be displaying images in Azure Data Explorer, as that tool might not support loading in external images from a storage account or other sane location. But this feels more like a neat parlor trick than something I’d actively recommend.

Comments closed

Monitoring DirectQuery Connection Openings in Power BI

Published 2024-08-12 by Kevin Feasel

Chris Webb digs into the numbers:

In the past I have blogged about how important the number of connections from a Power BI DirectQuery semantic model to a data source is for performance. It’s also true that in some cases opening a connection, or some of the operations associated with opening a connection, can be very slow. As a result it can be useful to see when your semantic model opens connections to a data source, and you can do this with Log Analytics.

Click through to see how you can do this and some of the information it provides.

Comments closed

Spooling in DirectQuery when Moving through On-Premises Gateway

Published 2024-08-05 by Kevin Feasel

Chris Webb diagnoses and resolves an issue:

Recently I was working with a customer using DirectQuery mode and where all traffic to the data source had to go through an on-premises data gateway for security reasons. They noticed that report performance got worse when traffic went through the gateway and this was particularly true when Power BI generated SQL queries that returned hundreds of thousands of rows. 

Click through to learn more about what Chris found and how to fix the issue.

Comments closed

Power BI Report Server “Couldn’t Connect to the Analysis Services Server”

Published 2024-08-02 by Kevin Feasel

Deepthi Goguri grabs a patch:

When you check the Change log for Power BI Report Server, Version: 1.20.8910.25479 (build 15.0.1115.165), Released: May 28, 2024 release notes mentioned about the security change made during this update to add the environment variable and system variable on the Power BI Report Server machine.

Read on to learn more about this and what it takes to correct the issue.

Comments closed