Press "Enter" to skip to content

Category: Power BI

Calculation Options in Power BI

Marc Lelijveld builds a list:

There are tons of options in Power BI to add your custom logic. Varying from SQL expressions at data ingest all the way up to Visual Calculations to add logic on a visual level. But how do they compare to each other, where should you do what?

As I’ve been a fan of making DAX easier since day one, I thought it may be a good moment to highlight the differences between calculation options using DAX in Power BI, like measures and calculated columns and how that is significantly different with Power BI Visual Calculations and how those can make your DAX easier.

Read on for a half-dozen options and some further thoughts about calculation options.

Comments closed

Comparing Pie and Waffle Charts

Rita Fainshtein makes waffles:

Why is it more challenging to get an accurate answer from pie/donut charts and why do we feel less confident trying to read them?

The main reason is the difficulty in calculating the areas of the pie slices/lengths of the donut arcs.

This is much more challenging and less confident than comparing the areas of rectangular shapes.

Rita takes a look at the waffle chart custom visual available for Power BI and then builds something custom fit for the task.

Comments closed

Color and Power BI Themes

Allison Kennedy talks color in Power BI:

Being a good data storyteller means using all the tools at your disposal. This includes color. It is important to note that I’m not advocating the use of color only in your reports – you need to ensure that there is another method to discern what’s going on with the data for those in your audience who might be colorblind. But that doesn’t mean we can’t play around with color and use it to add meaning to our reports. 

Most people I know would agree that GREEN means ‘Good’ and RED means ‘Bad’. But colors can be very personal. For example, my favorite color is BLUE and it was also the color I chose for my math notebooks throughout school. (Yes, math was my favorite subject – I guess it’s no surprise I ended up in a career that works so much with data.) 

In addition to personal meaning of colors, there can also be cultural meanings around individual colors, and that can shape how individuals view a given color. Grace Fussell has one of my favorite articles on the topic.

Comments closed

One Problem with Scrollbars in Power BI

Chris Webb focuses on the performance aspect of scrollbars on tables:

Concluding my series of blog posts on seemingly harmless things you can do in a Power BI report that can lead to performance problems and the “This visual has exceeded the available resources” error (see also “Calculate(), Filter() and DAX memory usage” and “DAX measures that never return blank“), in this post I’ll show how table visuals with vertical scrollbars that potentially show thousands of rows can be a Bad Thing.

I’d also note the aesthetic problem: the intent of a dashboard is to be glanceable, meaning that a user can gain sufficient understanding of what is happening without needing to click, drag, sort, filter, or otherwise manipulate the dashboard. This means, if you are intending to create a dashboard (versus a report), vertical scrollbars form a second sort of issue: you’re obscuring data that you consider important enough to show to the end user.

Comments closed

Visual Calculations and Multi-Bar Graphs

Erik Svensen builds a thing:

In this post I will guide you through creating this chart in Power BI – it is a stacked bar chart that show the size/impact of three different measures – Sales Value, Sales Units and Avg Price in one visual.

It’s not a visualization that I would recommend but there might be use cases for it somewhere and it has been a good exercise in what we can do with visual calculations.

It’s very clever, I’ll give it that.

Comments closed

Automate the Power BI Incremental Refresh Policy via Semantic Link Labs

Gilbert Quevauvilliers needs to get rid of some data fast:

The scenario here is that quite often there is a requirement to only keep data from a specific start date, or where it should be keeping data for the last N number of years (which is the first day in January).

Currently in Power BI using the default Incremental refresh settings this is not possible. Typically, you must keep more data than is required.

It is best illustrated by using a working example.

Check out that scenario and how you can use the Semantic Link Labs Python library to resolve it.

Comments closed

Antipattern: DAX Measures Never Returning Blank

Chris Webb explains the value of BLANK:

Following on from my earlier post on the Query Memory Limit in Power BI, and as companion to last week’s post on how a DAX antipattern using Calculate() and Filter() can lead to excessive memory consumption by queries (and therefore lead to you hitting the Query Memory Limit), in this post I want to look at the effects of another DAX antipattern on performance and memory usage: measures that can never return a blank value.

Read on to see how much of a difference using DAX to fill a grid with 0’s can make.

Comments closed

Deploying a Power BI Project File via Azure DevOps

Angela Henry deploys to prod:

When it was announced there was a collective cheer from Power BI source control advocates heard ’round the world. Since it’s preview release, Microsoft has also added GIT integration with Fabric workspaces. This makes it so easy to incorporate source control for all (or almost all) of your Fabric artifacts, including Power BI.

But what happens when your organization already has a mature CI/CD process in place using Azure DevOps? Do you really want to break from that pattern and have it controlled somewhere else? That’s what this post is about, using Azure DevOps CI/CD pipelines to deploy your Power BI Project files (.pbip).

I’m going to share my experience in hopes that it will save you some time if this is the route you need to take.

Read on for Angela’s experience. Note that this applies both to Microsoft Fabric as well as a Fabric-less Power BI.

Comments closed

Atomic Design for Report Development

Kurt Buhler has an interesting approach:

Developing a good semantic model or report takes a lot of time and effort. One way to reduce this cost is by re-using parts of an existing solution for a new model or project. This modular approach is particularly valuable when a developer faces common or recurring challenges and processes. Despite this, many developers commonly repeat efforts when they start new projects, models, and reports. For example, developers will often manually recreate measures, date tables, and patterns in a new model, or spend precious hours formatting visuals in a new report, while they have already created the same or similar things in the past. One reason for this is that it is difficult to identify candidate elements to re-use, or how you can re-use them in a convenient and scalable manner.

In this article, we want to introduce a conceptual framework from UI/UX called the atomic design methodology from Brad Frost. This framework can help developers to approach Power BI models and reports in a modular way to improve productivity and consistency of a developer’s work. The purpose of this article is to introduce the concept as well as some approaches that exist to re-use parts of your model and report. In future articles and videos, we will elaborate on these and other methods in additional detail.

I like the idea a lot, but Kurt does describe some of the challenges you’ll likely need to work through to adopt it.

Comments closed

DAX Memory Usage from Filtering on a Table

Chris Webb talks Power BI anti-patterns:

Following on from my last post on the Query Memory Limit in Power BI, in this post I want to look at one of the most common DAX antipatterns and its effect on query memory usage: filtering on a whole table, rather than an individual column, in the filter parameters of the Calculate() function. A lot has already been written on this particular antipattern from the point of view of query performance – for example see here and here – but it’s only with the recent addition of the Execution Metrics Profiler/Log Analytics event that you can see how bad it is for memory usage too.

Read on for an example. In Chris’s case, there’s a 3x memory difference between doing the right thing and the wrong thing, so this can easily add up.

Comments closed