Press "Enter" to skip to content

Category: Power BI

Querying Power BI from Visual Studio Code

Published 2020-08-25 by Kevin Feasel

Phil Seamark shows us how to write queries against Power BI using Visual Studio Code:

It’s helpful to understand there are two main client libraries for Analysis Services. A client library is what you can add to any new Visual Studio Code Project to provide objects, methods and functions relevant for the tool you are building.

Make sure you download the NetCore (.Net Core) versions of these libraries when working with Visual Studio Code. There are .Net Framework versions of these libraries that are more suited to use with the full Visual Studio product.

Read on for links to those libraries and a thorough demonstration.

Comments closed

Creating KPIs in Power BI

Published 2020-08-24 by Kevin Feasel

Alberto Ferrari takes us through key performance indicator creation in Power BI Desktop:

Starting from the July 2020 version, Power BI Desktop offers the possibility of using external tools to modify its internal Tabular model. With a tool like Tabular Editor, you can create a KPI directly in Power BI Desktop so that it can be used in any Power BI report and also by using the Analyze in Excel feature. The KPI feature was previously available only in Tabular models created in Analysis Services or Power BI Premium. This introductive article shows you how to create and consume KPIs in Power BI Desktop. A more detailed description of the available KPI graphics and the corresponding state values is the topic for an upcoming article.

Let us see the feature with a practical – though fictitious – example. Say Contoso needs to analyze the Margin % of its products. The yardstick is the overall margin, which is the Margin % over time and products with a tolerance of 2%. The overall margin of Contoso is 53%. Therefore, a category with a Margin % less than 51% is considered bad (red), over 55% is considered good (green), in between 51% and 55% is considered average (yellow). Moreover, Contoso wants to analyze the trend of Margin % compared with the previous year. For example, the margin might be red but Contoso can evaluate which action to take depending on whether it is improving or not over time.

Read on for the demonstration.

Comments closed

Using Perspectives in Power BI

Published 2020-08-20 by Kevin Feasel

Mark Lelijveld walks us through something new in Power BI Desktop’s August 2020 update:

If you work or used to work with Analysis Services, you might know the perspectives functionality. It is a feature inside tabular modelling that allows you to define viewable subsets of a data model.

Each tabular model can include multiple perspectives, where each perspective can include a subset of tables, columns a measures. Especially with large enterprise models, perspectives can be very useful.

With perspectives, you can define specific perspectives to be defines for a specific target audience. For example, the author can create logical subsets of the model for each audience of the dataset. (e.g. Sales, Finance, Marketing, etc.) One thing must clear, perspectives are not object level security or any other kind of security! It is just a better way to view it.

Read on to see how you can create and work with these in Power BI Desktop.

Comments closed

Using Jupyter as an External Tool for Power BI Desktop

Published 2020-08-18 by Kevin Feasel

David Eldersveld continues a series on Power BI external tools:

Many people use Python with notebooks, so let’s take a look at one possible way to enable a Jupyter external tool for Power BI Desktop. The following stepwise approach begins with simply opening Jupyter. It then progresses to creating and opening a notebook that includes Power BI’s server and database arguments. Finally, it works its way toward downloading a notebook definition contained in a GitHub gist and connects to Power BI’s tabular model to start to make this approach more useful.

This post continues a series of posts related to Python and Power BI. The first three parts of this blog series introduced some possible uses for Python connected to a Power BI model, how to setup a basic Python external tool, and how to both use it with a virtual environment and connect to the Tabular Object Model.

This was a cool usage of Power BI’s external tool functionality and starts to give you an idea of how powerful it can be.

Comments closed

Preparing Source Data in Power BI

Published 2020-08-17 by Kevin Feasel

Paul Turley continues a series on doing Power BI the right way:

Your options for transforming data at the source depend on the capabilities of the data source. In general, a query running closer to a data source will be more efficient than one that must cache a set of data and manipulate it downstream. If you are importing text files or Excel files, then your only choice is to import an entire file and then throw away what you don’t need – which is fine for small-ish data sets. On the right side of the previous diagram, some data morphing can be performed in DAX using calculated columns or calculated tables. In rare cases, there may be good reasons but if you can perform row-level calculations in a view or in Power Query, you will be better off to keep all that query logic in the same layer of the solution.

Read on for some good advice, as well as information on query folding, filters, incremental refresh, and much more.

Comments closed

Geometry and Geography Functions in Power BI

Published 2020-08-17 by Kevin Feasel

Chris Webb walks us through some new Power Query functionality:

In the August 2020 release of Power BI Desktop a couple of new Power Query functions were added: Geography.FromWellKnownText, Geography.ToWellKnownText, GeographyPoint.From, Geometry.FromWellKnownText, Geometry.ToWellKnownText and GeometryPoint.From. These functions (which are coming soon to Power Query in Excel too), make it easier to work with geographic and geometric data in the Well Known Text format. You can have all kinds of fun with these functions if you have a visual (like the Icon Map custom visual) that can display Well Known Text data, but I’ll leave that kind of thing for future blog posts. In this post I’ll explain how the basics of how the functions actually work.

So far, it looks like it’s converting strings of latitude and longitude data (in the geography case) into individual elements for plotting, but no distance measures at this time.

Comments closed

Blocking Classic Workspaces in Power BI

Published 2020-08-14 by Kevin Feasel

Adam Saxton points out something new in Power BI:

The ability to BLOCK classic workspaces from being created in Power BI is finally here! Adam shows you how to implement and what to consider. Create Microsoft Teams without the worry!

Click through for a video as well as the Power BI blog post describing this. You can also tell that Adam has the heart of a DBA based on the level of excitement around blocking something. DBAs and goalies, I tell you.

Comments closed

Hardcoding Calculation Groups in a Measure

Published 2020-08-13 by Kevin Feasel

Kasper de Jonge works around a limitation with the Power BI UI and calculation groups:

AS we have seen calculation groups are great :). It offers amazing flexibility and is extremely easy to maintain. But sometimes it doesn’t do what you want due the limitations of the visuals. Let’s say I want to have a visual that shows me the sales of current year and sales Previous year on different axis (let’s say as line).

You would create something like the visual below where you want to use the same measure but apply different calc groups for each measure. But unfortunately, below visual is not as we want it to be.

But Kasper has us covered with a bit of DAX, so check that out.

Comments closed

DAX Patterns: Second Edition

Published 2020-08-11 by Kevin Feasel

Marco Russo announces a second edition of DAX Patterns:

Great news! Just one year after releasing the second edition of The Definitive Guide to DAX, we just published a new website, a new book, and a new collection of videos: the second edition of DAX Patterns!

DAX Patterns is a collection of patterns in DAX for Power BI, Analysis Services Tabular, and Power Pivot for Excel. The first edition of DAX Patterns dates back to the end of 2014, and it was based on Power Pivot for Excel. Since then, DAX has evolved with many useful features. Most importantly, Power BI hit the market, and the number of users adopting DAX grew at an exponential rate. When we published the first edition of this book, Power BI had not even been announced yet. Today, most DAX users create a Power BI solution. The new edition of DAX Patterns is thus based on the tool you love: Power BI.

The book looks to be quite useful, and you can get an idea if this content is right for you from the DAX Patterns website. What’s crazy is that they’re offering everything in the book on the website for free, but I’d suggest that if you pick up enough good info from the site, give back by buying a copy of the book or videos.

Comments closed

Fixing Bad Data in Power BI

Published 2020-08-11 by Kevin Feasel

Matt Allington does the thing you shouldn’t (often) do:

Let me make this statement upfront and be clear. The best way to solve problems with source data are to go back to the source and correct the problems there. This is my recommendation on how you should solve such issues. However, sometimes that is not possible for whatever reason. This article will explain how you can use Power Query to override incorrect data during load when you can’t change it at the source, for whatever reason.

This is the classic BI tool quandry: the best solution is, as Matt mentions, to fix the source system. But when that’s not on the table—such as when you’re getting data from a third party—Matt has methods to work through data issues.

Comments closed