Category: Power BI

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.

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.

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.

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.

Chris Webb begins a series on troubleshooting timeouts:

If you’re working with a large Power BI dataset and/or a slow data source in Import mode it can be very frustrating to run into timeout errors after you have already waited a long time for a refresh to finish. There are a number of different types of timeout that you might run into, and in this series I’ll look at a few of them and discuss some of the ways you can work around them.

In this post I’ll look at one of the most commonly-encountered timeouts: the limit on the maximum length of time an Import mode dataset refresh can take. 

Click through to see the limits and ways to (sort of) get around them.

Reza Rad uses a date slicer:

Power BI from and to date slicer

It happens that you might have two fields as From and To date (or Start and End date) in your dataset, and you want a date slicer in the report. The date slicer has to filter records in a way that the FROM and the TO dates are in the range of dates selected in the slicer. There are multiple ways of doing this. In this article and video, I’ll explain a simple but effective method for that. I have explained in another article, how this can be done using two date slicers, you can read that from here.

Click through to see what you need and how you can put one of these in place.

Jeroen ter Heerdt explains why you might see the capitalization change in text loaded into Power BI:

Some of you might have seen this before – you load some data into Power BI and suddenly the capitalization (uppercase / lowercase) of your text changes on you. Let me explain what is happening here.

Click through to learn why. Normally I’d say “and what you can do about it,” but there’s not much. I guess the best answer is, embrace consistent capitalization and then you won’t notice anything.

Chris Webb is on a mission:

In my quest to check out every last bit of obscure Power Query functionality, this week I looked into the ContextInfo option on the Sql.Database and Sql.Databases M functions. This option allows you to set CONTEXT_INFO in SQL Server (see here for a good article explaining what this is) and here’s an example of how to use it:

Click through for that example.