Press "Enter" to skip to content

Category: Power BI

Linear Regression in Power BI

Published 2019-09-23 by Kevin Feasel

Joseph Yeates shows how to implement linear regression in Power BI:

The goal of a simple linear model is to fit a line onto this plot to summarize the shape of the data using the equation above.

The “a” value is the slope of the fitted line (rise over run) and the “b” value is the intercept on the y-axis (when x is equal to zero).

In the gapminder example, the life expectancy column was assigned as the “y” variable, as it is the outcome that we are interested in predicting or understanding. The year1950 column was assigned as the “x” variable, as it is what we are using to try and measure the change in life expectancy.

This is a little more complicated than adding a regression line to a scatterplot (the “normal” way to do linear regression with Power BI) but this method lets you work with the outputs in a way that the normal method doesn’t.

Comments closed

Multi-Table, Multi-Column Keys in Power BI

Published 2019-09-23 by Kevin Feasel

Alexander Arvidsson has a riff on a common Power BI question:

The keys we need for connecting the two tables are “region” and “certification”, respectively. A key on just one of these columns won’t ensure uniqueness and here is hurdle number one: how do we create a relationship in Power BI that is based on more than one key? Simple answer is that we can’t. But what we CAN do is create a concatenated column with the data we need to create a unique key, and then do our relationships based on that. 

Click through for the process.

Comments closed

Custom Power BI Date and Time Formats

Published 2019-09-20 by Kevin Feasel

Chris Webb continues a series on Power BI custom formats:

In my last post I showed lots of examples of how Power BI’s new custom format string feature can be used to format numbers. This post, looking at dates and times, will be a bit different for two reasons: there are a lot more useful examples of custom date and time formats built into Power BI Desktop, and some of the format placeholders listed in the VBA documentation aren’t supported in Power BI. As a result I’m going to concentrate on some useful formats that aren’t covered well by the examples and highlight a few things that aren’t possible right now.

Read on for a slew of demos.

Comments closed

Strong and Weak Power BI Relationships

Published 2019-09-20 by Kevin Feasel

Alberto Ferrari takes us through the two different kinds of relationships in Power BI:

A relationship in a Tabular model can be strong or weak. In a strong relationship the engine knows that the one-side of the relationship contains unique values. If the engine cannot ensure that the one-side of the relationship contains unique values for the key, then the relationship is weak. A relationship can be weak because either the engine cannot ensure the uniqueness of the constraint – due to technical reasons we outline later – or the developer defined it as such. A weak relationship is not used as part of table expansion. Let us elaborate on this

Something I’d like to see improved in Power BI is to differentiate strong versus weak relationships in the UI. Having no way to differentiate is okay if you only have a few tables or if you designed everything, but coming in late and reviewing a big model, it’s annoying to double-click each link to see if it’s strong or weak.

Comments closed

Alerting on Refresh Failure in Power BI

Published 2019-09-18 by Kevin Feasel

Matt Allington shows how you can set up an alert to contact you when a scheduled Power BI data refresh fails:

In this article I am going to share with you a concept to manage refresh failures in production PowerBI.com reports. In a perfect world, you should configure your queries so that they “prevent” possible refresh failure issues from occurring, but also to notify you when something goes wrong without the report refresh failing in the first place. There are many things that can go wrong with report refreshes and you probably can’t prevent all of them occurring. In the example I use in this article I will show you how to prevent a refresh failure caused by duplicates appearing in a lookup table after the report has been built, the model has been loaded to PowerBI.com and the scheduled refresh has been set up using a gateway. If during refresh a duplicate key occurs in any of the Lookup tables in the data model, the refresh fails, and the updated data does not go live.

Matt’s specific scenario is around duplicate data, but it can extend to other issues as well.

Comments closed

Troubleshooting Power BI Refresh Failures

Published 2019-09-18 by Kevin Feasel

Annie Xu gives us a few reasons why Power BI refreshes might fail:

License level: Power BI Premium license has different level and Power BI Premium capacity is shared within a Tenant and can be shared by multiple workspaces.The maximum number of models (datasets) that can be refreshed in parallel is based on the capacity node.  For example, P1 = 6 models, P2 = 12 models and P3 = 24 models.

Click through for the set of possibilities.

Comments closed

IIS Log Analysis in Power BI

Published 2019-09-18 by Kevin Feasel

Joy George Kunjikkur shows how you can build a Power BI dashboard to analyze IIS log files:

As developers, we all might have encountered situation of analyzing IIS web server logs. During the development time, the file is small and easy to analyze in Notepad or Excel. But when it grows to GBs in production servers we use other tools. One such popular tool to query IIS logs is LogParser. It is a free command-line tool from Microsoft. There are graphical applications around it to generate even charts. One such free tool is the Log Parser Studio. It is also from Microsoft.


Once we move up in career and had to deal with managers, product stakeholders or ever to CXO to show what the IIS logs say, we need more visuals or a dashboard reflecting the IIS logs. Though we can create visual using Log Parser Studio, it is tedious creating reports and charts one by one. 

Click through for a solution.

Comments closed

Custom Formatting Numbers in Power BI

Published 2019-09-17 by Kevin Feasel

Chris Webb shows how you can use custom formats to display numbers more easily in Power BI:

Now that we can apply custom format strings to fields and measures in Power BI in the September 2019 release, I thought it would be useful to provide some examples of what’s possible with this very flexible new feature because the existing documentation for VBA isn’t easy to make sense of. In fact there’s so much to say I’m going to have to write a series of blog posts to cover everything! In this first post I’m going to look at formatting numbers.

When you need an exact number, a thousands separator goes a long way.

Comments closed

Comparing Power BI Files

Published 2019-09-16 by Kevin Feasel

Imke Feldmann shows off a new Power BI file comparison tool:

What’s not covered?
Nothing. The comparison includes everything from the pbit-files: So beneath your M and DAX code, you’ll see all about your visual definitions (incl. filters set !), row level security and much, much more. Actually, I found some information a bit noisy (like many date fields, telling you when which changes happened). So I filtered them out in Excel. I’d recommend to check it out and play a bit with it to find the most suitable settings for you.

This looks quite useful.

Comments closed