Press "Enter" to skip to content

Category: Power BI

Power BI Desktop Hardening

Published 2021-12-20 by Kevin Feasel

Matthew Roche explains a bit of jargon:

What I am going to do here is talk a little about “desktop hardening,” which is a term I’ve heard hundreds of times in meetings and work conversations, but which I discovered is practically undefined and unmentioned on the public internet. The only place where I could find this term used in context was in this reply from Power BI PM Christian Wade… and if you don’t already know what “desktop hardening” is, seeing it used in context here isn’t going to help too much.

When I hear that term, I definitely do not think about APIs; instead, the first thing which comes to mind is security, which is a totally different story.

Comments closed

Simulating Slow Data Sources in Power BI

Published 2021-12-20 by Kevin Feasel

Chris Webb builds a particular kind of test:

As a postscript to my series on Power BI refresh timeouts (see part 1, part 2 and part 3) I thought it would be useful to document how I was able to simulate a slow data source in Power BI without using large data volumes or deliberately complex M code.

It’s relatively easy to create an M query that returns a table of data after a given delay. For example, this query returns a table with one column and one row after one hour and ten minutes:

Read on for a version of the function which slowly emits rows, as well as some T-SQL which slowly emits rows.

Comments closed

Sparklines and Filter Context

Published 2021-12-17 by Kevin Feasel

Ed Hansberry riffs on the new sparkline functionality in Power BI:

This isn’t a tutorial on how to create sparklines as Microsoft has you covered there. What I want to discuss though is how Sparklines are impacted by the filter context in a visual. First though you need to enable this feature. It is in preview mode at launch, so go to File, Options, Options and Settings, and look for the Preview section. Click Sparklines, then close and restart Power BI Desktop.

In the visual above, I added the “Month” field to the Sparkline settings and that created an additional filter on the visual. Before I added the Sparkline, Year was the only filter affecting the numbers. By adding the month to the sparkline, shown below, it breaks out the Total Net Sales figure by month.

But what if I don’t want the sparkline to be the full range of data. For example, I just want the sparkline to show the last 3 months of the year, so 3 data points, not 12.

Click through to see how it’d look as a measure and what you need to do to make sparklines look right.

Comments closed

Troubleshooting External Command Timeouts in Power BI

Published 2021-12-17 by Kevin Feasel

Chris Webb continues a series n troubleshooting timeouts:

In the first post in this series I showed how any Power BI dataset refresh started via the Power BI portal or API is limited to 2 hours in Shared capacity and 5 hours in Premium capacity, and how you could work around that by running a refresh via Premium’s XMLA endpoint feature. In the second post in this series I showed how some M functions allow you to set timeouts. However, even if you initiate a refresh via the XMLA endpoint you may still get a timeout error and in this post I’ll discuss another reason why: the External Command Timeout.

Read on to see what the external command timeout is and when it might strike.

Comments closed

Get the Previous Non-NULL Value in DAX

Published 2021-12-15 by Kevin Feasel

Kristyna Hughes calculates the lagged value in DAX:

If you are an avid report designer or user, you may have wanted to see the percent difference between one row and the previous one. Sounds easy right? Well let’s break it down. Percent difference follows a consistent formula: (current value – previous value)/previous value. Current value? Easy peasy. Previous value? That’s where this blog comes into play. It’s fairly simple to get the previous month value or previous date period value because Power BI can tell it exactly what the filter would be (September minus one month is always August ). However, there may not have been any invoices in August, so how can we compare September with the last month that had an invoice?

Click through to see how.

Comments closed

Composite Models via DirectQuery over Power BI Datasets

Published 2021-12-14 by Kevin Feasel

Paul Turley is living the dream:

Last year I wrote this post about the new composite model feature in Power BI that enables datasets to be chained together using a featured called “DirectQuery for PBI Datasets and AS“. The prospect of creating a data model that virtually layers existing data models together without having to repeat the design, sounds like nothing less than Utopia. We can leverage existing datasets, with no duplicate models, no duplication of business logic, and no duplication of effort. The promise of this capability is that data models may be referenced from other data models without duplicating data. So, is this really possible?

Read on for Paul’s thoughts and how they’ve changed over the past couple of months with new updates.

Comments closed

Importing Azure active Directory Users into Power BI

Published 2021-12-14 by Kevin Feasel

Reza Rad gets an assist:

There are two main methods to fetch the Azure Active Directory information; Microsoft Graph, or PowerShell Cmdlets. Both methods are very useful. However, explaining both in one article will be overwhelming. In this article, I’ll focus on how you can fetch the information using PowerShell Cmdlets. The method I explain here is manual. However, the PowerShell scripts can be automated to run as a scheduled process (I might explain that later in another article too). Let’s see how it works.

The method explained here is exporting the AAD users into a CSV file first, and then Power BI imports data from the CSV. You can use any other intermediate data sources such as Excel, SQL Server, etc if you want to. You just need to use their PowerShell cmdlets or parameters to do that.

Special thanks to Aaron Nelson for helping on preparing the demo for this article. Anytime I have a PowerShell question, he is the master who just finds a way to do it in a few seconds. Connect with him using his blog, Twitter, GitHub, or LinkedIn profile.

Click through for the Powershell-based solution.

Comments closed

Timeouts in Power Query Functions

Published 2021-12-13 by Kevin Feasel

Chris Webb reminds us to look at timeouts in Power Query functions:

In the first post in this series I showed how the Power BI Service applies a limit on the total amount of time it takes to refresh a dataset in the Power BI Service, except when you initiate your refresh via an XMLA Endpoint. In this post I’ll look at the various timeouts that can be configured in Power Query functions that are used to access data.

Every time a Power BI Import mode dataset connects to a data source it goes through a Power Query query, and inside the code of that Power Query query will be an M function that connects to a specific type of data source. Most – but not all – of these M functions have the option to set timeouts. 

Read on to learn more about these timeouts, as well as other Power Query functions which have timeouts by default.

Comments closed

Automating Historical Partition Processing in PBI Per User

Published 2021-12-09 by Kevin Feasel

Gilbert Quevauvilliers runs into a timing issue:

I recently had a big challenge with one of my customers where due to the sheer volume of data and network connectivity speed I was hitting the 5-hour limit for processing of data into my premium per user dataset.

My solution was to change the partitions from monthly to daily. And then once I have all the daily partitions merge them back into monthly partitions.

The challenge I had was I now had to process daily partitions from 2019-01-01 to 2021-11-30. This was a LOT of partitions and I had to find a way to automate the processing of partitions.

Not only that, but I had to ensure that I did not overload the source system too!

Read on to see what Gilbert did to solve this problem.

Comments closed

Modeling Many-to-Many Relationships in Power BI

Published 2021-12-07 by Kevin Feasel

Marco Russo and Alberto Ferrari show off two techniques:

Our readers know SQLBI position regarding bidirectional relationships: they are a powerful tool that should be used with great care and avoided in most scenarios. There actually is one scenario where bidirectional relationships are useful: when you need to create a model involving a many-to-many relationship between dimensions. In this scenario, using a bidirectional filter relationship is the suggested solution. Nonetheless, there may be reasons why the bidirectional relationship cannot be created, because of ambiguity. If you face this situation, you can use a different modeling technique based on a limited many-to-many cardinality relationship, which would work even when it is set as a unidirectional relationship. The choice between the two models is not an easy one. Both come with advantages and disadvantages that need to be deeply understood in order to make the right choice.

In this article, we first provide a description of the two techniques, and then we proceed with the performance analysis of both solutions, so to provide information about which technique to use and when.

Read on for the analysis.

Comments closed