Category: Power BI

Matt Allington finds another place where GUIDs aren’t your best option:

I was doing some work for a customer this week – they had a performance issue with a Power BI report. The data in the workbook wasn’t overly large, about 400,000 rows, yet the file size was 110 megabytes and the performance of the model was relatively slow given the number of records. When I looked at the report I noted that the report was using GUIDs between the primary and foreign keys on a number of tables. Generally speaking, it is not good practice to use a GUID to join tables, as GUIDs do not compress well and have a negative effect on the efficiency of physical 1 to many relationships.

Read on to learn more as well as what you can do about it.

Comments closed

Kasper de Jonge shows how you can add calculation groups in C# with the Tabular Object Model:

At the time of writing there are no tools built into Power BI to create them though. You can add them programmatically though.

To add them I wanted to play around with the tabular object model but more on this project later 🙂 . Unfortunately, there was not much documentation available on how to add calculation groups using TOM. Luckily, I have short access lines to the devs and they helped me :). I wanted to share in the code snippet below how to add a calculation group to your model in TOM using C#. Make sure you add the SSAS NuGet packages to your project.

Click through for an example of what to do.

Comments closed

Imke Feldmann creates a function out of a query created previously:

In my previous blogpost I’ve described a method how to extract a substring that follows a certain pattern from a string. In this post I show how to transform a query into a function that can be applied to many rows of a table.

Click through for a video as well as step-by-step instructions.

Comments closed

Gilbert Quevauvilliers solves an annoyance:

While doing some work I had a matrix that I had created. What I found was that when I had more Yr-Month values that could fit in the matrix due to having the scroll bar it would show me the older Yr-Months and not the latest ones as shown below.

I always like to think what the experience would be like for the report consumer. And if I was using this report to have to scroll each and every time to see the latest data would be a pain to say the least.

What I came up with was a relatively easy solution but made the reporting experience that much better. Where it will display the Yr-Months in descending order (The current month first)

Below are the steps I completed to achieve this.

By default I like to have dates moving from left to right, as it’s easier for a native-English speaking environment to interpret time that way. But in cases like Gilbert’s, I’d rather have glanceability, seeing the most important data without any sort of scrolling, clicking, zooming, or manual operation. The ideal might be to have the tool give us the option to start scrolled all the way to the right, but in lieu of that, Gilbert provides a nice second-best solution.

Comments closed

Paul Turley is starting a series on doing Power BI the right way:

Any attempt to apply universal best practices to Power BI solution design is a slippery slope. The tools are so flexible and powerful, and the requirements of each project are so varied that it is challenging to establish a set of steps or rules that, if followed, will always yield the absolute best design for a given scenario. With that out of the way, I’ll say this: In my job, I see a lot of poorly-designed Power BI projects. I’ve worked with dozens or scores (maybe even hundreds?) of consulting clients who bring us projects – some partially completed, some finished, and many that are just broken – to be fixed or completed. My reactions range from “that’s just downright wrong” to “hmmm… I wouldn’t have done it that way but I guess it will work for the time being”. I try not to cast stones and do, on occasion, realize that others have found a better way to solve a problem. I don’t have all the answers but I do have a lot of experience with Microsoft Business Intelligent solution design. I have learned many good practices and design patterns from community leaders over the past twenty or so years.

This is only the introductory post in the series, but I’m excited to see what Paul has in store.

Comments closed