Category: Power BI

Chris Webb has some fun with M:

How does this work? Here’s what each of the steps do:

– The source step creates a simple table with two columns called firstname and lastname using #table (see here for more details on that).
– The tabletype step declares a new table type with two columns (the same two columns in the table from the previous step) and then adds a metadata record to this type. In that record the Documentation.FieldDescription field contains the text values that will appear as tooltips when you hover over each column.
– The replacetype step replaces the type of the table returned by source with the type declared in tabletype.

Click through for the code.

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.

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.

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.

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.