Press "Enter" to skip to content

Category: Power BI

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

Power BI Desktop: Analyze in Excel

Published 2020-08-10 by Kevin Feasel

Marco Russo has gone and done it now:

Less than a month ago, Microsoft introduced the External Tools feature in the Power BI Desktop July 2020 release. By using DAX Studio, you were already able to create a PivotTable in Excel connected to the model hosted by Power BI Desktop. However, this would require three clicks (DAX Studio / Advanced / Excel). This is why I thought the External Tools feature was something many users would like to use without having to open – or even install – a larger tool like DAX Studio is.

It’s interesting to see what the community has made so far from the External Tools feature.

Comments closed

The Downside of EAV-Style Measures in Power BI

Published 2020-08-10 by Kevin Feasel

Chris Webb explains why you should try to stick to the fact-dimensional model in Power BI:

In this fact table the dimension keys remain the same, but the Value column stores all the data from the Sales, Tax and Volume Sold measures in the original table and the Measure Name column tells you what type of measure value is stored on any given row. Let’s call this approach the Measures Dimension approach.

There are some advantages to building fact tables using the Measures Dimension approach, for example:

– You can now use a slicer in a report to select the measures that appear in a visual
– You can now easily add new measures without having to add new columns in your fact table
– You can use row-level security to control which measures a user has access to

Generally speaking, though, any time you deviate from a conventional dimensional model you risk running into problems later on and this is no exception. Let’s go through the disadvantages of modelling data using a Measures Dimension.

Read on for several good reasons (and yes, “things are formatted wrong” is a good reason!).

Comments closed

Treemaps and Tables in Power BI

Published 2020-08-07 by Kevin Feasel

Ben Richardson looks at a couple of Power BI visuals:

In this article, you will learn how to work with Treemaps and Tables, which are two of the most commonly used Power BI visuals. You will also see how slicers can be used in Power BI to dynamically update the data in Treemaps and Tables. Power BI Visuals are extremely easy to create and don’t require you to write any code.

I like treemaps more than I probably should. They have a very limited set of good uses but I just can’t quit them.

Comments closed

API Consumption with Power BI Dataflows

Published 2020-08-06 by Kevin Feasel

Matthew Roche covers some pointers when working with APIs:

These APIs are often slower than a database, which can increase load/refresh times. Sometimes the load time is so great that a refresh may not fit within the minimum window based on an application’s functional requirements.

These APIs may also be throttled. SaaS application vendors often have a billing model that doesn’t directly support frequent bulk operations, so to avoid customer behaviors that affect their COGS and their bottom line, their APIs may be limited to a certain number of calls for a given period.

The bottom line is that when you’re using APIs as a data source in Power BI, you need to take the APIs’ limitations into consideration, and often dataflows can help deliver a solution that accommodates those limitations while delivering the functionality your application needs.

Read on for more details including links to a few pointers.

Comments closed