Press "Enter" to skip to content

Category: Power BI

Power BI Paginated Reports on the Cheap

Published 2019-11-14 by Kevin Feasel

John White shows how you can get paginated reports in Power BI at a lower cost:

In our scenario, we have a paginated report that uses a published Power BI dataset as a data source. as of this writing, there is no API call available to render a paginated report on demand, so we will rely on the scheduled subscription capability. in order to minimize the cost of the solution, we want the dedicated capacity to run as little as possible.

The solution will consist of an Azure logic app, and Power BI paginated report scheduling. An Azure logic app uses the same set of actions that a Flow in Power automate does, but is a little more flexible in its permissions model.

Read on for the step-by-step instructions.

Comments closed

Thoughts on Large Datasets in Power BI

Published 2019-11-12 by Kevin Feasel

Teo Lachev has some early thoughts on large datasets in Power BI:

At Ignite 2019 Microsoft announced the public preview of large datasets in Power BI Premium. This is a significant milestone as now datasets can grow up to the capacity’s maximum memory (previously, the max size was 10 GB with P3 plan), thus opening the possibility of deploying organizational semantic models to Power BI. I consider this feature mostly suitable for organizational BI as I don’t imagine business users dealing with such large data volumes. I tested large datasets during its private preview, and I’d like to share some notes.

Teo has some open questions, and I’d like to see this shifted down to SSAS too.

Comments closed

Improving Post-Join Aggregation Performance in Power Query

Published 2019-10-29 by Kevin Feasel

Imke Feldmann finds some nice performance improvements with aggregating data after a join using Power Query:

When you join a table to another table in Power Query, the UI gives you the option to either expand the columns (default) or aggregate the contents of the joint tables. That’s useful if multiple rows are returned for the rows of the table that has been joined to (left table):

But this method is extremely slow. Compared to “simply” expanding all values to new rows (which took around 5 seconds), the aggregation took around 50 seconds. The automatically generated code uses the “Table.AggregateTableColumn”-function.

Read on to see two separate attempts to speed things up.

Comments closed

Speeding Up Excel Pivot Table Performance

Published 2019-10-28 by Kevin Feasel

Chris Webb shows how you can improve performance of Excel pivot tables hitting Analysis Services Multidimensional models:

Back in 2016 I wrote the following blog post about changes to the way Excel 365 generated MDX queries for PivotTables connected to Analysis Services, Power Pivot/the Excel Data Model and Power BI datasets:

https://blog.crossjoin.co.uk/2016/07/08/excel-2016-pivottable-mdx-changes-lead-to-big-query-performance-gains/

I know it sounds boring and not something you need to worry about but trust me, this is important – these changes solved the vast majority of Excel PivotTable performance problems that I encountered when I was a consultant so you should read the above post before continuing.

Unfortunately, earlier this year these changes had to be partially rolled back because in some rare cases the queries generated returned incorrect results; this means that you may find that values for subtotals and grand totals are again being returned even when they aren’t being displayed. The good news is that you should still be able to get the improved performance with a few minor tweaks.

Read on to see what those tweaks are.

Comments closed

Head-to-Head Comparisons with Power BI

Published 2019-10-24 by Kevin Feasel

Rob Collie walks us through building a visual which provides head-to-head comparison using Power BI:

Yes, I know that NONE of the infographics above is a scientifically “good” comparison tool.  Too noisy, too flashy, not clean…  but every now and then you DO need to cater to your audience.  Engagement is the first step in the comprehension funnel, and in this particular example, yep, I’m trying to capture the eyeballs of an audience that likes this sort of thing.  The style of #4 is a decent compromise in this case.  Know your audience.

Rob takes us through an interesting journey. I don’t think I’d want to use that style too often, but to be fair, Rob talks about that in the snippet I clipped.

Comments closed

Key Performance Indicator Transparency

Published 2019-10-24 by Kevin Feasel

Treb Gatte explains how to build trust in KPIs:

Many Power BI reports and dashboards have simple, color-based indicators, commonly referred to as Key Performance Indicators or KPIs. These indicators provide a way to communicate the current condition of a monitored metric.

These KPIs require the data consumer to:
– Trust the data used to calculate it
– Understand the rule used to derive the indicator
– Understand the calculation used to set the value

Read on for good questions and a recommended design. Trust is critical, as people won’t look at dashboards whose data they don’t trust.

Comments closed

Seeing Filter Results with DAX Measures

Published 2019-10-24 by Kevin Feasel

Gilbert Quevauvilliers wants to see the specific values involved in a Power BI filter:

I was working with a customer trying to get them to better understand DAX and one if the things that is difficult to understand is how the filtering works in DAX.

The challenge I have found is that when using filters in a DAX measure I cannot visually see what is happening within the DAX Filter.

Below I will show you how I can see the values in the FILTER

Click through to see how.

Comments closed

Moving Dataflows Between Workspaces

Published 2019-10-23 by Kevin Feasel

Marc Lelijveld (along with Ton Swart) shows how to move dataflows from one workspace to another:

Now, here it starts to become interesting. Let’s try to automate the manual steps to an automated process. To automate task in the Power BI service, we have to “talk” to the Power BI REST API. The Power BI REST API provides programmatic access to the report server catalog. For example, basic CRUD (Create, Read, Update and Delete) operations can be done on folders, reports, KPIs, data sources, datasets, refresh plans, subscriptions, etc.

Click through to see how to do it by hand and then how to automate it.

1 Comment

Finding Columns and Measures Used in SSAS Tabular

Published 2019-10-23 by Kevin Feasel

Kasper de Jonge uses Power BI to figure out which columns are being used in an SSAS Tabular model:

I got an interesting question about being able to figure out which measures and columns are being used for all tabular models in a server, either by known reports or by self service users using Power BI. To solve this I decided to use Power BI :).

To get the required information I decided to capture and parse the queries being that are being send to AAS and parse the results Power BI desktop over a period of time. In this post I describe how to do it.

Check it out, and also read the comments for an additional tip from Bill Anton.

Comments closed