Press "Enter" to skip to content

Category: Power BI

Getting Max Column Value from Power Query

Published 2019-08-09 by Kevin Feasel

Chris Webb hits us with techniques to get the maximum value in a column using Power Query and M:

In this first part I’m going to set up the scenario and show you what I found out from my own experimentation. The really mind-blowing tricks shown to me by the ever-helpful Curt Hagenlocher of the Power Query dev team will be covered in part 2.

Let’s say you have a large csv file which contains a numeric column and you want to get the maximum value from that column. In this case I’m going to use the 2018 Price Paid data from the UK Land Registry available here. This csv file contains 1021215 rows, one for each property transaction in England and Wales in 2018; the second column in this file contains the the price paid for the property, so the aim here is to get the maximum price paid for all property transactions in 2018.

Read the whole thing.

Comments closed

Version Control and Power BI Desktop

Published 2019-08-07 by Kevin Feasel

Gilbert Quevauvilliers takes us through version control with PBIX files:

In the second part of my blog post I am going to detail how to use the version control with Power BI Desktop files.

This will include adding files, checking files in and out, viewing previous versions and reverting to previous versions.

If this is the first time you are reading this blog post, I would highly suggest reading Setting up Version Control for my Power BI Desktop Files (PBIX) with no additional Cost * | Part 1

In short, Gilbert treats PBIX file as any other data file. These can get kind of beefy, though, so I’ve also saved them as templates—that way, you get the structure without pulling in all of the data.

Comments closed

Hearing Differences in Power BI

Published 2019-08-05 by Kevin Feasel

David Eldersveld shows us one technique for adding a sound component to your Power BI dashboard:

Data sonification uses variations in audio to hear differences in data values. From an accessibility standpoint, data sonification offers another potential avenue to enrich your reports beyond methods pertaining to data visualization.

There are a few pieces that need to be assembled to enable data sonification that works in both Power BI Desktop and Service. While, audio has been used in the Power BI Service for awhile, Power BI Desktop has been silent until now. This post attempts to show how to produce audio tones in Power BI for greater accessibility. It also demonstrates how to blend data with a standard range of audio pitches.

It’s an interesting idea.

Comments closed

Adding Aggregates to Table.Profile

Published 2019-08-05 by Kevin Feasel

Chris Webb shows us how to add additional aggregates to Table.Profile in M:

A few years ago I blogged about the Table.Profile M function and how you could use it to create a table of descriptive statistics for your data:

https://blog.crossjoin.co.uk/2016/01/12/descriptive-statistics-in-power-bim-with-table-profile/

Since that post was written a new, optional second parameter has been added to the function called additionalAggregates which allows you to add your own custom columns containing aggregate values to the output of Table.Profile, so I thought I’d write a follow-up on how to use it.

Click through for that follow-up.

Comments closed

Extended Filtering in DAX

Published 2019-08-05 by Kevin Feasel

Matt Allington continues a discussion on the FILTER() function in DAX:

The new formula follows the rule “don’t filter a table if you can filter a column”. But in this case the column and the table have the same cardinality, so there is little benefit there. Also, the new formula requires a second CALCULATE() and SUM() inside the FILTER() function. This is required because the column Customers[YearlyIncome] is no longer in the same table that FILTER() is iterating. The FILTER() function is iterating a virtual, single column table that contains all customer keys in the customer table. The column Customers[YearlyIncome] doesn’t exist in this virtual table, it exists in the Customers table, so you must wrap the column in an aggregation function, SUM() in this case. Further, as the FILTER() function iterates in a row context through the virtual table, the virtual relationship does not filter the connected tables UNLESS you specifically tell the formula to do so. Technically, to make the filter propagate from the new virtual table created by ALL(Customers[CustomerKey]), we need to convert the row context into an equivalent filter context via context transition. Context transition is triggered by the inner CALCULATE() inside the FILTER() function in this case.

Read on for several tips for efficient filtering.

Comments closed

Using Bookmarks for Power BI Filters

Published 2019-08-02 by Kevin Feasel

Marc Lelijveld continues a series on storytelling with Power BI:

As said, being dynamic is a broad concept. Lets use the above shown example. As a report author, we can define that the end-user should be looking at an top 10 ranking of countries (right side of the report). Since the difference between number 9 and 10 in the ranking is so small, you might want to know what the difference is to number 11. Now, we can’t see that. We need to change the filter context to see the rest of the ranking.

Click through for a step by step example of what to do.

Comments closed

Exporting Large Data Sets from Power BI

Published 2019-08-01 by Kevin Feasel

Imke Feldmann shows how you can export data in an amount which exceeds Power BI’s native limitations:

Although the method is fairly simple, there are simpler methods if you just need the raw data from your data model (and not the specific aggregations or measures that the visual contains):

– Use DAX Studio to download all tables from your data model at once
– Use DAX Studio to download specific tables from your data model (one by one)
– Or use R or Python to download specific tables if you’re comfortable with these languages. This method also allows scheduled refreshes in the service.

In short, this is probably a fifth-best solution, but it does work.

Comments closed

Updated Power BI Violin Chart

Published 2019-08-01 by Kevin Feasel

Meagan Longoria reviews an update to Daniel Marsh-Patrick’s violin chart custom visual in Power BI:

First, the violin plot is now a certified custom visual. This means that it has been tested by the Power BI team to ensure it meets certain requirements, one of which is that the visual does not access external services or resources. You can be confident your data isn’t being sent externally when you use the violin plot.

As for the functional enhancements, a new legend has been added. This is a great addition to make the chart clearer and more easily read, especially for audiences that may not be familiar with how the violin plot works. The customizable legend calls out what markers are used for mean, median, and quartiles.

Meagan is quite pleased with these updates.

Comments closed

Power BI Screen Usage Comparison

Published 2019-07-31 by Kevin Feasel

Gilbert Quevauvilliers compares the original Power BI service UI to the new one in terms of how much of the screen is available to our dashboard rather than the service UI:

I have seen quite a lot of chatter with regards to the Power BI Service new look and feel, which I personally really like.

People were saying that whilst they like the look and feel, it appears to be taking up more real-estate screen space. As well as others preferring the tabs along the bottom like Excel does.

I took it upon myself to do a quick and possibly quite simple test to see if the new look and feel make the report page smaller or larger.

The result was a bit of a surprise to me.

Comments closed

Easy Navigation with Power BI

Published 2019-07-30 by Kevin Feasel

Marc Lelijveld has started a series on storytelling with Power BI. Part one is all about navigation:

Providing an easy navigation is important for the usability of your report. In order to make it as intuitive as possible, you should think as an end-user. By opening a report, where do you expect the navigation to be?

If you open the first webpage you can think of, most likely you will find the navigation on top or on the left side. Which is totally reasonable (at least according to my opinion) since we read from left to right and from top to bottom.

By default in Power BI you will have the navigation on the bottom where you can switch between your report pages. But we just concluded that it is more intuitive to put your navigation on top or on the left side. So why not do the same with your Power BI reports? We can do this by creating our own navigation and bookmarks for that!

Read the whole thing.

Comments closed