Press "Enter" to skip to content

Category: Power BI

Version Control for Power BI Datasets

Published 2023-04-27 by Kevin Feasel

Richard Swinbank improves on a prior version control system:

In the previous post, I outlined a possible workflow for Power BI development, and implemented an Azure DevOps pipeline to show how steps in such a workflow could be automated. To build the pipeline I stored an entire .pbix report file – data and all – in version control, which is a problem for at least two reasons:

  • storing large report files in a version control system won’t scale well
  • datasets may contain confidential or sensitive data which must be kept out of version control.

In this post I’ll look at separating a report’s dataset from its visuals, version controlling the standalone dataset (without data), and deploying the dataset automatically to Power BI.

Read on for the process.

Comments closed

Updating Power BI Dataset Compatibility Level

Published 2023-04-26 by Kevin Feasel

Kurt Buhler wants the newest toys:

In the monthly updates for Power BI, there may be new features that appear for preview. For example, in the April 2023 update, dynamic format strings for measures released into preview. This feature allows you to specify a DAX format string expression for measures, like you already could do with calculation groups.

In Power BI, these features become readily available once enabled from the ‘Preview features’ section of the ‘Options’ menu. However, when you are connected to a dataset or metadata with Tabular Editor, the properties will not be visible. That’s because behind-the-scenes, Power BI upgrades the model compatibility level when using a preview feature for the first time.

Click through to learn how.

Comments closed

Using Redis as a Power BI Datasource

Published 2023-04-25 by Kevin Feasel

Kyle Teegarden has me wondering:

Power BI is a widely used, interactive visualization tool capable of querying a variety of SQL-powered back ends. To accelerate these queries, and provide an even more responsive Power BI user experience, we’ve developed Redis SQL ODBC.

Redis SQL ODBC is a native ODBC driver that lets you seamlessly integrate Azure Cache for Redis Enterprise and Enterprise Flash tiers with Power BI. This may dramatically improve your Power BI query response times.

Knowing that the intent of Redis is individual point lookups, this was a bit surprising. Doing the queries over secondary indexes does help square that circle, however.

Comments closed

Load Testing in Power BI

Published 2023-04-24 by Kevin Feasel

Chris Webb gives us the why and the how:

If you’re about to put a big Power BI project into production, have you planned to do any load testing? If not, stop whatever you’re doing and read this!

In my job on the Power BI CAT team at Microsoft it’s common for us to be called in to deal with poorly performing reports. Often, these performance problems only become apparent after the reports have gone live: performance was fine when it was just one developer testing it, but as soon as multiple end-users start running reports they complain about slowness and timeouts. At this point it’s much harder to change or fix anything because time is short, everyone’s panicking and blaming each other, and the users are trying to do their jobs with a solution that isn’t fit for purpose. Of course if the developers had done some load testing then these problems would have been picked up much earlier.

With that in mind, Chris explains some of the things we can do to help with load testing in Power BI.

Comments closed

Using Dynamic Format Strings for Measures in Power BI

Published 2023-04-24 by Kevin Feasel

Meagan Longoria shows off a new preview feature:

The April 2023 release of Power BI desktop introduced a new preview feature called dynamic format strings for measures. This allows us to return values with different formats from the same measure. Previously, we needed to create calculation groups (usually by using Tabular Editor) to accomplish this. But now it is built in to Power BI Desktop.

Read on to learn good use cases for this feature, as well as a few important notes on operation and limitations.

Comments closed

Running a Power Query Operation Only when Data Exists

Published 2023-04-21 by Kevin Feasel

Gilbert Quevauvilliers doesn’t waste time:

I had a requirement where I wanted to only have the underlying query run if there was data in the table.

As shown in the image below a query would run, scan 20GB and then return zero rows.

Instead of performing such an expensive scan, Gilbert provides an alternative. This is important in the case of querying the Synapse serverless SQL pool, as Gilbert does—in that case, the 20GB scan costs money.

Comments closed

Building a Report Development Workflow for Power BI

Published 2023-04-18 by Kevin Feasel

Richard Swinbank thinks about process:

In this series I’ll be talking a lot about workflow. Loosely speaking, report development workflow is the set of steps you have to take to create or update a Power BI report. This typically starts in Power BI desktop, and doesn’t end until users are able to get business value out of a report in the Power BI service.

A good workflow frees a report developer to do the things they like and are good at – understanding business problems, and crafting beautiful reports to help solve them. Anything that that doesn’t contribute to that just gets in the way – a bad workflow clutters up the development experience with peripheral tasks like version control, managing environments and performing deployments.

Richard shares a good number of thoughts on the matter, so check them out.

Comments closed

Disabling Filter Pane Aggregates in Power BI

Published 2023-04-17 by Kevin Feasel

Chris Webb disables a visual element:

These numbers are counts of the number of rows for each value in the table that the field is from. The query to get these counts is usually quite fast and inexpensive, but if you’re filtering on a field from a very large table (for example a fact table) and/or using DirectQuery mode that might not be true. For example, the screenshot above is taken from a DirectQuery dataset and here’s the SQL query that generates the counts shown:

Read on to see how to do this.

Comments closed

Performing a Pareto Calculation in DAX

Published 2023-04-17 by Kevin Feasel

Phil Seamark does some manufacturing analysis:

I always enjoy it when we get new DAX functions, especially so for the new set of WINDOW Functions recently added. As part of the April 2023 release of Power BI Desktop, we now have a RANK function and the ability to use a measure to control the order within the existing WINDOW function.

The first thing that sprung to my mind was to see how a Pareto calculation might leverage the new capability.

The basic idea of a Pareto calculation is to create a curve like representation of data ordered from largest to smallest.

Read on to see how.

2 Comments

Key Components to Power BI

Published 2023-04-14 by Kevin Feasel

Reza Rad provides an overview of what Power BI includes:

Power BI is a cloud-based technology from Microsoft for reporting and data analysis. This reporting technology is built in a way that is not only useful for developers to create reports but also for power users and business analysts. Power BI created a simple, easy-to-use, and user-friendly environment for creating reports. And on the other hand, it is based on several powerful components which help create reports and data analysis for complex scenarios.

Every component of Power BI is responsible for a specific part of the technology. There are components for building reports, connecting to data sources, doing analytics calculations, sharing reports, etc. The following sections explain what each component is. Some of these components are explained in detail in other articles.

Click through for a long list and a series of high-level summaries.

Comments closed