Press "Enter" to skip to content

Category: Power BI

Asymmetrical Matrices in Power BI

Matt Allington walks us through a complex scenario using the Matrix visual in Power BI:

Not every report you may want to build has such simple layout requirements as the one above.  When using an Excel pivot table, there is a feature called “Field, Items and Sets” that makes the process of creating asymmetric pivot tables relatively easy, but that doesn’t exist in Power BI.  To help you understand, here is an example of an asymmetric visual (Power BI matrix in this case).

Note in my example above, the first 4 years come from the year column, just like before, but now I have 2 additional columns at the end of the matrix that are not simply the addition of other columns.  To create the last 2 columns above I need to write measures, but you can’t have both measures and columns from your model showing like this in a Power BI matrix.  Instead you need to build a solution that leverages a separate table to generate the columns you need, and then to generate the results you need in each column.

Below I will show you how to build such an asymmetric matrix like this.

Read on for the solution. This is quite clever but also makes me wonder if the product shouldn’t make this scenario a bit easier for us

Comments closed

Updating a Power BI Refresh Schedule

Martin Schoombee continues a series on automating Power BI deployments:

There’s a few things you need to pay close attention to when setting the refresh schedule via the API:

– Unless you’re setting the refresh schedule for a Premium workspace, you can only refresh a dataset up to 8 times a day. We’re only going to set it to update once a day here, but keep this in mind if you’re planning to adjust the API call to refresh multiple times a day.

– The name of the time zone you provide has to match exactly with the names (middle column) in this reference: Microsoft Time Zone Index

– The refresh time has to be in the format hh:mm, and similar to the options in the Power BI portal you can only refresh on the hour or half-hour.

Read on to see how it works and the API call to make.

Comments closed

Combining CSV Files in Power BI

Stephanie Bruno shows us a method for combining CSV or Excel files:

One of the unsung heroes to me in Power BI desktop (or Power Query in Excel) is how wonderfully simple it is to combine csv or Excel files from a folder. Maybe it’s not totally unsung, but I think it’s an everyday problem that many people have to deal with. It could easily be an entry point for many new users to see just how much easier Power Query can make their lives.

As much as I appreciate how easy the tool allows us to automatically combine files without having to write any code, I never like all those extra queries that get created in my file and so I prefer to simplify it by just writing one magical little line of code. But I always got frustrated because my one line of code left me without proper column headers, so my one line of code turned into a few extra cumbersome steps. In this post, I’ll show you how to slightly modify that one line of code to get the column headers and keep your queries clean and simple.

Click through for a demo.

Comments closed

A New Financial Sample Dataset with Power BI

David Eldersveld takes a look at a new sample data set in Power BI Desktop:

With the October 2020 release of Power BI Desktop, Microsoft has incorporated a sample dataset directly into the product. While there are a few commonly used training and demo datasets already, there are benefits to having a basic starter Financials dataset included.

This data can accelerate the learning experience for new users, particularly those who may only need to focus on authoring reports on existing datasets at their organization and don’t need to immediately know much about data modeling.

Click through for David’s mixed review.

Comments closed

Getting Power BI to Write Your DAX

Gilbert Quevauvilliers shows off an interesting way of using a Power BI feature:

Recently I had to get some data from a Power BI Dataset. At first, I started writing the DAX using the fantastic DAX Studio.

Then a thought occurred to me, what if I could get the DAX already written and change it to my requirement. This would save me a lot of time and effort. I love the quote from Patrick in Guy in a Cube “I am not lazy, I am efficient”

Click through for the scenario. Looks like it will get you at least part of the way there.

Comments closed

Changing Power BI Data Source Credentials

Martin Schoombee walks us through changing data source credentials on deployment with Power BI:

The method we need to use here is the Patch method. Why is it Patch and not Post like we did with the parameters? Great question, and the “devil is in the details”…the method dictates how the underlying resource or attribute is modified, and the Patch method in this case means that there are partial (and in-place) modifications to an existing resource. Read more about the differences between PostPut and Patch here.

This is one of those areas where Power BI can be quite a letdown if you only use the UI.

Comments closed

Mapping New Column Names with Power Query

Soheil Bakhshi reminds me of DB/2:

So, here is my scenario. I received about 10 files, including 15 tables. Some tables are quite small, so I didn’t bother. But some of them are really wide like having between 150 to 208 columns. Nice!

Looking at the column names, they cannot be more difficult to read than they are, and I have multiple tables like that. So I have to rename those columns to something more readable, more on this side of the story later.

Fortunately, there’s a way to fix this; click through for that way.

Comments closed

Dynamic Format Strings when using Calculation Groups

Alberto Ferrari shows off how you can dynamically generate format strings when using calculation groups in Power BI:

Each product in Contoso weighs a certain weight. The weight is stored in two columns: the unit of measure and the actual weight, expressed in that unit of measure. Specifically, Contoso uses three units of measure: ounces, pounds, and grams.

Because the units of measure are different, you cannot aggregate the weight over different products. If you author a simple measure that computes the ordered weight of products by using a simple SUMX, the result is wrong:

Click through to see how you can work through this problem.

Comments closed