Press "Enter" to skip to content

Category: Power BI

RANKX in DAX

Published 2022-09-27 by Kevin Feasel

Marco Russo and Alberto Ferrari do some ranking:

Ranking is one of the most frequent calculations in Power BI reports. Needing to determine the top products, countries, customers and such is extremely common. RANKX offers a powerful and very fast way to produce ranking. Nonetheless, its use takes some understanding.

In this article we introduce the RANKX function and provide a few interesting examples of how it can be used. RANKX is not a complex function to learn. Nonetheless, most newbies find it intimidating because they do not fully grasp its internals. Once they learn exactly how RANKX works, its use becomes really simple.

Click through to learn how it works.

Comments closed

Tooltips on Power BI Pages

Published 2022-09-26 by Kevin Feasel

Teo Lachev offers some help:

Want to display visual-left hint to perplexed users that explains what your visual is supposed to reveal? Like me, you have missed the handy Power BI help tooltips feature that allows you to pop up some helpful text for each visual.

This works best for blocks of information a person needs to see once. Rather than it showing up every time you visit the page, make it available via a tooltip. You’ll want to make sure the tooltip is readily visible for people new to the report but not so overwhelming that it annoys people who already know how this thing works. That, however, is a completely different challenge.

Comments closed

Corporate Networks and Power BI Performance

Published 2022-09-26 by Kevin Feasel

Chris Webb notes a problem:

Over the years I’ve seen a few examples of how issues with an organisation’s corporate network can affect Power BI report performance. I’ve never blogged about them because, to be honest, I know next to nothing about networks and I’m not sure I could describe them properly. However, recently, I have seen a few instances of what I think could be a widespread but little-diagnosed problem – so I would like you to check if it’s happening to you and tell me what the cause is, if you can find out.

Some time ago I wrote a post about how Power BI report performance could be worse in Internet Explorer 11 and older Windows operating systems because some report performance optimisations we do are only available with HTTP/2. 

Read on for one potential issue which could add several seconds to report calls. There’s also a good comment which adds more helpful information.

Comments closed

Power BI Naming Conventions

Published 2022-09-23 by Kevin Feasel

Nicky van Vroenhoven asks, what’s in a name?

But I think it’s much broader than using only programming naming conventions these days.

In the context of Power BI, you can use naming conventions in (literally) all things that need a name, like gateways, workspaces, apps, etc.

Read on to understand why, as well as some thoughts on what makes sense for naming different Power BI-related objects.

Comments closed

Parameterizing Stored Procedures from Power Query

Published 2022-09-23 by Kevin Feasel

Soheil Bakhshi calls a stored procedure:

From time to time, Excel users require to get the data from a SQL Server stored procedure. The stored procedures usually accept some input parameters and return the results. But how can we dynamically pass values to the stored procedures from cells in Excel to SQL Server?

Read on for two approaches to the problem. Like Soheil, I think the second approach is much smoother, in part because it isn’t 30-something steps long.

Comments closed

E-Mailing Power BI Query Outputs as CSV Files

Published 2022-09-21 by Kevin Feasel

Gilbert Quevauvilliers finds a way:

I recently had a requirement from a customer where they wanted a list of all Customers and the Primary Contact to be emailed to them every day.

The reason for this to be emailed daily is to ensure that when calling the customer, they know whom to speak to.

This got me thinking and I could use Power Automate to achieve this task which I detail in this blog post below.

I appreciate the ingenuity involved in getting this to work, though this also presents a good case for having this data in a warehouse, where data export to CSV would be easier.

Comments closed

Using a Service Principal Account for Power BI + Dedicated SQL Pool

Published 2022-09-16 by Kevin Feasel

Dan English provides a hookup:

In this post I will go over a topic that is frequently asked about and that is using a Service Principal account with Power BI when connecting to data sources. Currently today none of the built-in connectors support this capability natively, but the SQL Server ODBC driver does support the use of a Service Principal account. The one caveat with using an ODBC driver with Power BI is that a gateway would be required once the report is published to the service.

Read on for the step-by-step process.

Comments closed

Getting User Access Lists to All Power BI Reports

Published 2022-09-15 by Kevin Feasel

Tomaz Kastrun wants to know what you can see:

This way, you will have a better view of users, and their access to data and reports (if these are containing sensible data). You can always retrieve the list of workspaces and access the list of all users with the PowerShell cmdlet Get-PowerBIWorkspace.

I have also added the Join-Object module. It can join two objects or two arrays, based on the given matching columns.

Click through for a Powershell script which does the job.

Comments closed

Creating Multiple Audiences in a Power BI App Workspace

Published 2022-09-15 by Kevin Feasel

Gilbert Quevauvilliers plays to the audience:

With the recent announcement (Announcing Public Preview of Multiple Audiences for Power BI Apps | Microsoft Power BI Blog | Microsoft Power BI) it is now possible to create multiple audiences in a single App Workspace.

What this means you can now have a single app workspace but create a view for specific users (each view is known as an Audience)

This blog post will detail how to manage multiple audiences with AAD Security Groups, where the only requirement will be to update the app when new reports get created and to which audience to make them available.

There’s also a big warning on Gilbert’s post that you will not want to miss.

Comments closed