Press "Enter" to skip to content

Category: Power BI

The Power BI Gateway

Published 2017-12-13 by Kevin Feasel

Reza Rad explains how the Power BI gateway works:

You don’t need a gateway in all scenarios. Only if the data source is located on-premises, you need a gateway. For online or cloud-based data sources, no gateway is required. For example; if you are getting data from CRM Online, you don’t need a gateway. However, if you are getting data from SQL Server database located on your local domain server, then you need a gateway. For Azure SQL DB you don’t need a gateway. However, a SQL Server database located on Azure Virtual Machine is considered as on-premises and needs gateway.

This post could not have come at a better time for me, so I’m definitely happy to see it.

Comments closed

Using List.Accumulate

Published 2017-12-12 by Kevin Feasel

Reza Rad shows off a Power Query function which is not available in the UI:

List.Accumulate is a function that can easily save a number of steps in your Power Query transformations, instead of applying multiple steps, you can simply use List.Accumulate to overcome what you want. List.Accumulate function loops through the list and accumulate a value as a result. This function needs usually three parameters; the list itself, seed, and accumulator. Here are parameters explained in details;

best way to learn about seed and accumulator is through some examples, let’s apply some transformations with List.Accumulate and see how these two parameters are working.

Read on to see how to use it.

Comments closed

Aggregating Data Migration Assistant Assessments

Published 2017-12-07 by Kevin Feasel

Dustin Ryan has put together a Power BI template to collate results for a number of Azure Data Migration Assistant assessments:

Recently a customer reached out to me to help with the challenging task of understanding the assessment results of 61 SQL environments including over 500 databases  being considered for migrating to Azure SQL Database. Now there is already a great solution that exists for aggregating DMA assessment exports but it only works for assessment exports in .JSON format. The existing solution also requires that the assessment results be written to a SQL Server database.

So I built a solution that uses Power BI to parse the DMA assessment exports (.CSV format) and aggregate the data so it can be more effectively browsed and understood in a report.

Click through for a link to download that template, as well as additional resources.

Comments closed

Keeping Report Decks Consistent

Published 2017-12-06 by Kevin Feasel

Tristan Robinson has tips for keeping your Power BI Enterprise report decks looking consistent and nice:

The next consideration is around the number of objects on a report – keep it simple.  Avoid building a giant monolithic report, the more objects you use, the slower the report will perform on PBI service, iPad’s and even to develop.  This is especially true for tables/matrices which will each need to fire off separate DAX queries to return the data elements. Too many objects also has knock on effects for exporting to PowerPoint as objects will overlap with one another more which may not be as much of a case within PBI service but will affect other apps. You can use the selection pane (in the view tab) so move objects above/below one another which will bring forward/push back the elements.

This is advice tailored toward Power BI in particular, but much of it also applies in general.

Comments closed

Power BI Helper: Expression Dependency Trees

Published 2017-11-30 by Kevin Feasel

Reza Rad announces a new feature of Power BI Helper:

I’m excited to share the news with you that we have added a new feature in Power BI Helper; Expression Tree. Expression Tree will expand the tree of expression for a Measure or calculated column, you can see what other measures are used to create this expression, and where other measures, calculated columns, or even normal columns are located (in which table). This feature is in addition to previous two features of this tool which were; Showing tables and fields used in visualization pages of a Power BI Report, and ability to search for a column or table that used in visualization pages of a report. In this post, I’ll explain how this new feature works.

Read on for the explanation.  I can see this being quite useful.

Comments closed

Conditional Formatting With Power BI Line Charts

Published 2017-11-28 by Kevin Feasel

Daniil Maslyuk shows how to perform conditional formatting on a line chart in Power BI:

Have you ever wished you could change the line colour depending on the overall trend? For example, if your sales increase over time, the line is green; if there is a decline, then the line is red. While this functionality is not yet natively available in Power BI Desktop, it does not mean this cannot be done! In this article, I am going to show you how to achieve this effect.

Read on to see how he does it.

Comments closed

Design Concepts: Cognitive Load

Published 2017-11-28 by Kevin Feasel

Meagan Longoria explains the concept of cognitive load and how it relates to building reports:

Our first design concept is cognitive load, which comes from cognitive psychology and instructional design. Cognitive Load Theory says that when we present our audience with information, we are asking them to use brain power to process it. That brain power (aka working memory) is limited, so we need to be intentional about the information we present to them.

In order to commit information to memory and be able to recall it later, that information must go through sensory memory and working memory and then be encoded into long-term memory.

This concept drives a lot of good advice in dashboard and report design, particularly that if it does not directly help a person learn the important information you are trying to convey, it’s not worth having on the report or dashboard.

Comments closed

Roll Your Own SSAS Performance Monitoring Tool

Published 2017-11-27 by Kevin Feasel

Shabnam Watson has a great post on building an Analysis Services performance monitoring tool from scratch using Power BI:

In many cases, SSAS works efficiently with default settings right out of the box. However, when you have large databases, substantial number of concurrent users, insufficient resources on your server, or when best practices are not followed during SSAS database design, you can run into performance bottlenecks and problems. In these scenarios, you need to know what to measure and how to measure them, what’s normal for your environment (benchmark), and you need to have some amount of historical measurements to be able to see the events that lead to a certain bad performance/failure point. Once you have this data, you can improve your server’s performance by addressing the problem(s).

This is a tour de force of an article, absolutely worth reading if you plan on dealing with Analysis Services at some point.  Even if you don’t build your own tool, you’ll learn a lot about what drives SSAS performance and what indicates that there might be a problem.

Comments closed

Getting Distinct Dimension Count Based On A Filtered Measure

Published 2017-11-24 by Kevin Feasel

Gogula Aryalingam shows us a neat trick in Power BI:

Enthusiastic as we were, one of the hardest nuts to crack, though it seemed so simple during requirements gathering, was to perform a distinct count of a dimension based on a filtered measure on a couple of the reports. To sketch it up with some context; you have products, several more dimensions, and a whole lot of measures including one called Fulfillment (which was a calculation based on a couple of measures from two separate tables). The requirement was to get a count of all those products (that were of course filtered by other slicers on the Power BI report) wherever Fulfillment was less than 100%, i.e. the number of products that had not reached their targets.

Simple as the requirements seemed, the hardest part in getting it done, was the limited knowledge in DAX, specifically, knowing which function to use. We first tried building the data model itself, but our choice in DAX formulae, and the number of records we had (50 million+) soon saw us running out of memory in seconds on a 28GB box; Not too good, given the rest of the model didn’t even utilize more than half the memory.

Click through for the answer.

Comments closed