Press "Enter" to skip to content

Category: Power BI

Trending And Smoothing

Published 2016-05-11 by Kevin Feasel

Matt Allington looks at trending and smoothing data in Power BI:

You can download the workbook here if you want to take a look, or simply look at the embedded version I have pinned at the bottom of the post.

Notice the spikes in sales in different months in the chart above?  These spikes are very common in sales data, and in my experience they can be even more prevalent in weekly sales data.  These spikes make it difficult to analyse trends in the data.  You could put a trend line into the chart (thanks to the April update do Power BI), but a standard linear trend line is too simplistic to really see what is happening in your data, particularly if there are seasonal changes.

One good way to look at the trends in your data is to add an Average Monthly Sales Rolling Quarter trend line to the chart.  You simply take the total sales of the last 3 months and then divide by 3.  If you were doing a weekly trend, take the last 13 weeks and divide by 13.  When you overlay this Avg Monthly Sales RQ line on the original chart, it looks like this.

This is a fairly advanced topic, but it’s also the kind of thing which separates good reporting from great reporting.

Comments closed

The Code Behind Power BI Parameters

Published 2016-05-10 by Kevin Feasel

Chris Webb shows us how to get to the M code used in query parameters:

From this you can see that the value returned by the parameter query is just a single piece of text – it’s the value “Monday” that is set as the Current Value, that’s to say the value returned by the parameter itself. The interesting stuff is all in the metadata record associated with the value. I blogged about metadata here, so you may want to read that post before going any further; it’s pretty clear that the fields in the metadata record correspond to the values set in the UI. All of the fields in the metadata record can be edited in the Advanced Editor if you want.

When the parameter is used in another query it is referenced like any other query value. For example, if you load the DimDate table from the Adventure Works DW sample database and use the parameter above to filter the EnglishDayNameOfWeek column then the code generated in the UI looks like this:

I’m sure that by next month, there will be a half-dozen new things added to this alone, given how fast the Power BI team can push features…

Comments closed

Using Flow + Power BI For Reporting

Published 2016-05-04 by Kevin Feasel

John White combines Microsoft Flow with Power BI to generate real-time reports:

I may blog about that solution in the future, but with the Future of SharePoint event rapidly coming up, my BI Focal fellow collaborator, Jason Himmelstein convinced me that there was something more interesting that we could do with this. How about near real time monitoring of Twitter conversations for the event? All of the pieces were in place.

We rolled up our sleeves, and in relatively short order, had a solution. Jason has written about the experience on his SharePoint Longhorn blog, and he has included the videos that we put together, so I can be a little less detailed in this post.

Today I learned about Microsoft Flow.  Looks a lot like Zapier.

Comments closed

Power BI With Mapping

Published 2016-04-29 by Kevin Feasel

Dustin Ryan shows how to use the Bing Maps API in Power BI visualizations:

The good news is that Power Query in Power BI is flexible enough for us to take advantage of the Bing Maps API so that we can lookup the missing pieces of information we need. In this blog post, I’m going to show you how you can use the Bing Maps API to look up an address based on a latitude and longitude or use a street address to find a latitude and longitude for the location.

I love how map visualizations nowadays are so easy.

Comments closed

Power BI Updates

Published 2016-04-29 by Kevin Feasel

Julie Smith points out new Power BI updates and she is excited about them:

Today, Microsoft announced the April Power BI Desktop updates and they include something I and my colleagues at Innovative Architects have been long awaiting: formatting for table reports!

While it was understandable that initially Power BI wanted to focus on visualizations first and foremost, the display for pure tabular data in Power BI was lackluster. There was no control over any of the following:

  1. Font size

  2. Colors –of any element in a table, font, background color, title, totals. Nada

  3. Borders

I am simultaneously impressed that the Power BI team can so consistently push out crazy numbers of updates and glad that I don’t personally need to keep up on all of the changes…

Comments closed

Row-Level Security With Power BI

Published 2016-04-29 by Kevin Feasel

Koen Verbeeck discusses dynamic, row-level security using Power BI:

Apparently, in PBI the domain is used, not the email. Good to know if row level security is implemented in PBI someday (note: currently it’s only available in the Power BI Service).

OK, now let’s upload this report to the Power BI Service so we can implement the RLS. When the report is opened, it seems  that my carefully crafted measures are ignored. Maybe Power BI ignores the USERNAME() function as long as there is no RLS defined.

I like the fact that you can test by role, making it a lot easier to see what others would see under those circumstances and finding bugs with security implementation.  If you have any non-public information and you use Power BI, this is a must-read.

Comments closed

Power BI: Dynamic Chart Titles

Published 2016-04-26 by Kevin Feasel

Chris Webb shows how to generate dynamic chart titles using Power BI:

But what about if you want the chart title to change depending on what is selected? For example, you might be using slicers or filters to allow a user to choose which days of the week they want to see data for. In that situation you might want to add a title that shows which days of the week have actually been selected; this would be particularly important if the report uses filters, or if the report is going to be printed. Unfortunately the built in Title Text property can’t be used to display dynamic values but in this blog post I’ll show you how to solve this problem using DAX.

The solution isn’t trivial, but it is cool.

Comments closed

Power BI KPIs And Colorblindness

Published 2016-04-25 by Kevin Feasel

Meagan Longoria points out that the choice of red and green for KPIs isn’t ideal:

And which colors do we love to use with KPIs? Red and green, of course! Color is a very powerful tool in data viz. We use it to indicate meaning and to draw attention to something important. KPI boxes are used to display key metrics in an efficient manner. These key metrics are usually rather important, and our users need to be able to see their status at a glance.

I quite like the design of the KPI boxes in Power BI, but for some reason they were created without the ability to adjust the color associated with the state (good/bad). Shown below, they use the common red/green color scheme.

It sounds like Microsoft is already working on fixing the issue.

Comments closed

Is Power BI SSAS In The Cloud?

Published 2016-04-13 by Kevin Feasel

Koos van Strien hits us with an interesting thought about SSAS versus Power BI:

As I’m currently planning to migrate the entire BI architecture of one of my customers to the cloud, this made me think: can we ditch SSAS as we know it already in favor of Power BI? What are the alternatives?

To study that, I’ve put some diagrams together to show the possibilities of moving BI to the cloud. First, I’ll discuss the possible architectures, then the impossible architecture (but maybe the situation I was looking for).

One man’s opinion:  there will be SSAS for Azure.  I have no proof of this, and the nice part about having no proof is that I can throw out wild speculation without fear of violating NDA….  But to me, Power BI solves a different problem and acts in conjunction with SSAS rather than as its replacement.  I also don’t see any technical reasons why SSAS couldn’t live in the cloud, and so that leads me to believe that it will be there eventually.  But hey, it turns out wild speculation is occasionally wrong…

Comments closed

Loading Large Data Sources Into Power BI

Published 2016-04-11 by Kevin Feasel

Reza Rad shows how to get beyond the 10 GB data limitation in Power BI:

Fortunately Power Query still is available with Live Connection. This gives you ability to join tables, flatten them if you require, apply data transformation and prepare the data as you want. Power Query can also set the data types in a way that be more familiar for the Power BI model to understand. If you want to learn more about Power Query read Power Query sections of Power BI online book.

Reza shows some techniques and also the negative repercussions to using Live Connection.  This is a good read if you’re getting into Power BI.

Comments closed