Press "Enter" to skip to content

Category: Power BI

Creating Data-Driven Power BI Report Subscriptions

Published 2020-06-11 by Kevin Feasel

John White shows how to create a data-driven subscription for a Power BI report:

One of the features that has never made the leap from SQL Server Reporting Services (SSRS) on-premises to the cloud is data-driven subscriptions. Users can subscribe to reports, but a data-driven subscription allows individual subscriptions to be stored in a central location and parameterized, while delivering the reports to multiple locations. This article will describe a pattern for accomplishing this using SharePoint lists as the subscription store, and Power Automate as the automation tool, for a no-code solution to this requirement.

The other alternative would be to use Power BI Report Server, but if you’re not using that, this is an interesting approach and solution.

Comments closed

A Power BI FAQ

Published 2020-06-11 by Kevin Feasel

James Serra answers questions about Power BI:

Should we have dev, test, and prod workspaces?

Yes! You should use change management to move reports through the dev/test/prod workspace tiers via the new deployment pipelines in Power BI. Use the workspaces to collaborate on Power BI content with your colleagues, while distributing the report to a larger audience by publishing an app. You should also promote and certify your datasets. The reports and datasets should have repeatable test criteria.

Read on for the full set of questions and answers.

Comments closed

Improving Power BI Performance

Published 2020-06-11 by Kevin Feasel

Dan Szepesi continues a series on Power BI performance tuning:

As an example, I am going to go through in detail how to use the results from the Performance Analyzer to understand the performance of your visuals.  I downloaded the sample PBIX from the Power BI Documentation at Microsoft.com – https://docs.microsoft.com/en-us/power-bi/create-reports/sample-datasets and I will use the visuals from the Net Sales report in the screenshots that follow.

I am going to walk through how I would approach looking at the performance of this visuals on this report and show what we can learn from the data that the Performance Analyzer gives me.

Click through for that example as well as several helpful tips.

Comments closed

Returning Multiple Values in Power BI with ConcatenateX

Published 2020-06-10 by Kevin Feasel

Nick Edwards shows how you can use the ConcatenateX DAX function to combine values:

In this blog post we’ll take a quick look at using ConcatenateX function to view a concatenated string of dates where the max daily sales occurred for a given month.

I came across this function whilst going through the excellent “Mastering DAX 2nd Edition Video Course” by the guys from SQLBI.com. So credit to Marco and Alberto for sharing this.

So how does it work? If we had a list of dates ranging from 01/01/2020 to 31/12/2020 and we wanted to see which days we achieved maximum sales for each given month in a year we could use the ConcatenateX function to return these dates in a single row per month.

Click through for the demo.

Comments closed

Power BI Best Practice Tips

Published 2020-06-09 by Kevin Feasel

Lazaros Viastikopoulos continues a series on Power BI tips, switching from performance to best practices:

Tip 2) Organise Measures by Grouping

Tip number two goes hand in hand with the tip explained above, as after we generate explicit measures, what should we do with all those leftover implicit measures? Surely they will confuse the report authors if they are left visible. Furthermore, if we structure our data model as a Star Schema, every fact table will contain some foreign keys to establish a relationship with the primary key in the dimension (lookup) table. Should these columns remain visible for everyone to use?

Read on to learn how, as well as details for the other four tips.

Comments closed

Organizing and Optimizing Power BI Dataflows

Published 2020-06-09 by Kevin Feasel

Marc Lelijveld wants your Power BI dataflows in tip-top shape:

In this blog I describe a few of the challenges you might face when you have a lot of dataflows. I will describe a few tips and tricks I am applying to sanitize your dataflow approach, organize dataflows and easy to browse through.

Dataflows are increasingly used as shared resource or staging layer inside the Power BI platform. While having dataflows, you can push down logic and reuse across different datasets. This lowers the impact on the source by extracting the data once from source to Power BI, helps in centralizing logic, having one version of the truth and lots of other advantages.

Read on for some tips and practices.

Comments closed

Optimizing Power BI Merge Performance with Table.Join

Published 2020-06-08 by Kevin Feasel

Chris Webb shows us another way to optimize Power BI merge performance:

The SortMerge algorithm, last in the list above, is the focus of this blog post. I mentioned in my earlier posts that the reason that merge operations on non-foldable data sources are often slow is that both of the tables used in the merge need to be held in memory. There is an exception though: if you know that the data in the columns used to join the two tables is sorted in ascending order, you can use the Table.Join function and the SortMerge algorithm and the data from both sources can be streamed rather than held in memory, which in turn results in the merge being much faster.

That’s the same in the relational world: merge joins are the fastest, assuming that your data is pre-sorted in the proper manner.

Comments closed

Power Query Performance Differences in When You Remove Columns

Published 2020-06-05 by Kevin Feasel

Chris Webb continues a series on optimizing Power Query merge performance:

In my last post I demonstrated how the size of a table affects the performance of Power Query merge operations on non-foldable data sources in Power BI. Specifically, I showed that removing columns from the tables involved in a merge before the merge took place improved performance. But does it matter when you remove the columns? Is it enough to only select the columns you need when you expand the nested table returned by a merge, for example, or just to remove columns after the merge step? So, today’s question is:

Does it make a difference to Power Query merge performance if you remove unwanted columns from your source tables in the step before the merge or in the step afterwards?

Read on for the result, as well as a pleasant surprise around Power BI’s capabilities.

Comments closed

Alerting when Power BI Tenant Settings Change

Published 2020-06-04 by Kevin Feasel

Melissa Coates walks us through how to track Power BI tenant settings changes:

This post discusses two methods of receiving an alert when a tenant setting has changed in the Power BI Service: one using Cloud App Security, and the other using the M365 Security & Compliance Center.

Tenant settings in the Power BI Service are among the most important things to get right. Once they are set the way you want, the objective is to ensure all changes are well-controlled. Tip: Check section 10 in the Planning a Power BI Enterprise Deployment whitepaper—I included some tenant setting recommendations in the latest version.

Read on to see how.

Comments closed

Working with ADLS Gen 2 in Power Query

Published 2020-06-03 by Kevin Feasel

Marco Russo takes us through some ways to optimize performance when working with Azure Data Lake Storage Gen 2 from Power Query:

With Power Query you can apply filters to the list obtained by the File System View option, thus restricting the access to only those files (or a single file) you are interested in. However, there is no query folding of this filter. What happens is that every time you refresh the data source, the list of all these files is read by Power Query; the filters in M Query to the folder path and the file name are then applied to this list only client-side. This is very expensive because the entire list is also downloaded when the expression is initially evaluated just to get the structure of the result of the transformation.

A better way to manage the process is to specify in the URL the complete folder path to traverse the hierarchy, and get only the files that are interesting for the transformation – or the exact path of the file is you are able to do that. For example, the data lake I used had one file for each day, stored in a folder structure organized by yyyy\mm, so every folder holds up to 31 files (one month).

Read on for more advice in this vein.

Comments closed