Press "Enter" to skip to content

Category: Power BI

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

Power BI Pivoting

Published 2016-04-08 by Kevin Feasel

Reza Rad shows how to pivot and unpivot using Power BI:

So Pivot is easy and simple to do, but you have to be careful about the nature and quality of source data set. If it is normal to have a name repeated in the source data, then an aggregation needs to be set properly. if you expect each name to appear once, then setting it as Do Not Aggregate works better because you can use error handling mechanism in Power Query to handle error somehow.

This is a good sight easier than writing a bunch of SUM(CASE) statements or using the PIVOT operator in T-SQL.

Comments closed

M Query Timing

Published 2016-04-05 by Kevin Feasel

Chris Webb gives a couple of options for timing M queries:

In M, the DateTime.LocalNow() function returns the current system date and time at the time the function is called – which means that you could call it multiple times in the same query and get different results. There’s also the DateTime.FixedLocalNow() function which also returns the system date and time; the difference between the two is, as the documentation says:

This value is fixed and will not change with successive calls, unlike DateTime.LocalNow, which may return different values over the course of execution of an expression.

This is good to know for those query-tuning scenarios.

Comments closed

Power BI Doesn’t Replace Warehouses

Published 2016-04-01 by Kevin Feasel

Jesse Seymour argues that Power BI won’t replace traditional data warehouses:

Pesonally, I am still struggling to see where PowerBI fits in my organization.  I am the only BI professional here, so I have to do every bit of the process from data modeling to building front end applications.  Right now, my organization has a data warehouse with some processes in the warehouse, a Datazen environment and an SSRS environment.  There is no SSAS cubes or any power users using PowerPivot to analyze data.

Data warehouses serve a particular role in an environment:  they answer known business questions and give consistent answers across an organization.  I see Power BI as a tool with a few separate uses depending upon organizational size and maturity.  I think its best use in shops which are not large enough, well-established enough, or with enough non-IT business intelligence expertise is BI developers building beautiful dashboards for business data consumers, feeding from existing systems (including data warehouses).  In that sense, it is a complement to a Kimball-style data warehouse.

Comments closed

Analyze In Excel

Published 2016-03-28 by Kevin Feasel

Avi Singh notes that Power BI will soon allow you to analyze data sets in Excel:

As a Modern Excel enthusiast (Modern Excel = Excel + Power Pivot + Power Query = Magic!), I found myself hesitant in embracing the new world of Power BI. Many of those inhibitions have shed away as Microsoft has continued to innovate and deliver an outstanding experience with Power BI. But I could not get over the feeling that going from Excel to Power BI felt like a one-way street.

You could upload/import an Excel Power Pivot model into Power BI desktop or onto PowerBI.com. But then you could not get it back. You could not get it back in Excel. In thefirst blog post on the site (yes the very first) Rob called us gridheads, and that we are.

Excel is still the top tool for business users.  Anything you can do to make life easier for your Excel users makes life easier for you as well.

Comments closed

Filled Maps In Power BI

Published 2016-03-23 by Kevin Feasel

Reza Rad digs into filled maps in Power BI:

I’ve mapped suburbs to County because that was the lowest level I’ve found in data category for geographic information. (Place and Address cannot be used for Filled Map at the time or writing this post). and I got Nothing! Not event a small area on the map.  I’ve tried then removing the district and putting suburb, region, country format with County as the data category which didn’t helped again.

I’ve found that I can map some locations based on Postal Code as you see below. However not Postal Code is not always good distinguishing field for a region, as multiple regions might have a postal code shared.

Filled maps have the potential to be powerful tools, but they aren’t perfect.  Check out Reza’s post for the full scoop.

Comments closed