Press "Enter" to skip to content

Category: Power BI

Uploading Multiple Reports to Power BI

Published 2022-07-11 by Kevin Feasel

Jon Fletcher doesn’t have time to upload reports one by one with the UI:

In this blog post, I will be sharing a PowerShell script that allows multiple Power BI reports to be uploaded at once. In a previous blogpost, I shared a PowerShell script that allowed users to downloaded multiple Power BI reports. Combined you could move several reports from one workspace to another in a few seconds.

The script is downloadable at the bottom of the page as a txt file. To use the script there are three steps to take.

Click through to see how it all works.

Comments closed

Refreshing Power BI Usage Metrics Report Credentials

Published 2022-07-07 by Kevin Feasel

Stephanie Bruno fixes a credentials issue:

Have you ever had a Usage Metrics Report just stop refreshing? At the bottom you’ll see a little message that tells you to check the credentials. But you don’t have access to the Usage Metrics Report dataset in the list of datasets in the workspace because it’s hidden! So even if you wanted to delete it and nicely ask it to start again, you can’t do it from the workspace. What can you do? Well, you can delete the Usage Metrics Report dataset with the Power BI Rest API.

Read on to see how.

Comments closed

Measuring Power Query CPU Utilization

Published 2022-07-06 by Kevin Feasel

Chris Webb does a refresh and tracks the damage done:

Some time ago I wrote a post about how optimising for CPU Time is almost as important as optimising for Duration in Power BI, especially if you’re working with Power BI Premium Gen2. This is fairly straightforward if you’re optimising DAX queries or optimising Analysis Services engine-related activity for refreshes. But what about Power Query-related activity? You may have a small dataset but if you’re doing a lot of complex transformations in Power Query that could end up using a lot of CPU, even once the CPU smoothing for background activity that happens with Premium Gen2 has happened. How can you measure how expensive your Power Query queries are in terms of CPU? In this post I’ll show you how.

Read on to see how you can see how much CPU is required to do that dataset refresh.

Comments closed

Object-Level Security in Power BI and Analysis Services

Published 2022-07-06 by Kevin Feasel

Marco Russo and Alberto Ferrari take advantage of object-level security:

To be more technical, object-level security can be applied to tables and columns, but it cannot be applied to measures directly. If a measure – whether in a direct or indirect way – references a column or a table that is not accessible under the current security context, the measure becomes invisible as well. This way, it is guaranteed that if a column must be invisible to a group of users, its content cannot be inferred by looking at the result of measures based on said column.

Concretely, there may be cases where a measure should be hidden from a group of users without removing the visibility of existing data structures. Let us look at a simple example first. We define the Sales Amount measure as the product of Sales[Quantity] by Sales[Price]. You also have a Discounted Sales measure that applies a set discount Sales Amount; now how can you hide Discounted Sales from a group of users without hiding the initial Sales Amount measure? By hiding either Sales[Quantity] or Sales[Price], you would hide both measures. Because the discount is set inside the Discounted Sales measure and not stored in the model, it looks as though you cannot hide just the measure. However, it we create a dependency in Discounted Sales on an empty hidden table specifically created to generate that dependency, we can hide Discounted Sales by hiding that table.

Read on to see how.

Comments closed

Power BI as an Enterprise Data Warehouse

Published 2022-07-01 by Kevin Feasel

James Serra follows Betteridge’s Law of Headlines:

With Power BI continuing to get many great new features, including the latest in Datamarts (see my blog Power BI Datamarts), I’m starting to hear customers ask “Can I just build my entire enterprise data warehouse solution in Power BI”? In other words, can I just use Power BI and all its built-in features instead of using Azure Data Lake Gen2, Azure Data Factory (ADF), Azure Synapse, Databricks, etc? The short answer is “No”.

Read on to understand why Power BI shouldn’t be your data warehouse.

Comments closed

Finding Key Influencers with Power BI

Published 2022-06-30 by Kevin Feasel

Gauri Mahajan looks at the key influencers visual in Power BI:

Once the Key Influencers are added to the Power BI report, it would look as shown below. The visual would be empty by default. The key areas that are required to make this visual works are Explain section and Analyze By section. The Analyze section is used to point to the variables or attributes that we intend to analyze. The Explain By section is used to point to the variables or attributes that may be influencing the attributes specified in the Analyze section.

I’ve found this visual to be pretty interesting if you have a good dataset.

Comments closed

Power BI Desktop External Tools Not Opening

Published 2022-06-30 by Kevin Feasel

Gilbert Quevauvilliers ran into a problem:

I recently got a new laptop and I had to install all my programs again. Everything was going as expected, except when I went to use ALM Toolkit, the program would not open.

I would click on ALM Toolkit, I would see it open for a few seconds in task manager and then it would disappear.

That led me down a few rabbit holes, I thought could it be Windows Defender, could it be the anti-virus or could it be installed incorrectly.

It turns out that neither of those was the problem. Read on to learn what the issue was and how Gilbert corrected it.

Comments closed

Unicode Character Generation in Power Query

Published 2022-06-30 by Kevin Feasel

Meagan Longoria needs more Unicode:

You may have used the UNICHAR() function in DAX to return Unicode characters in DAX measures. If you haven’t yet read Chris Webb’s blog post on the topic, I recommend you do. But did you know there is a Power Query function that can return Unicode characters? This can be useful in cases when you want to assign a Unicode character to a categorical value.

Click through to see how this works.

Comments closed

Power BI Smart Narratives

Published 2022-06-29 by Kevin Feasel

Gauri Mahajan shapes the narrative:

To make it easier for the end-user, this job may be done by report or business analysts who may pre-analyze the reports, manually form textual narratives that summarize the key highlights in the report. While it solves the challenge in question, it opens a possibility of analysts’ bias getting introduced in the report, and the end-user may or may not agree with the narrative. Some systems solve this issue by employing complex machine learning / natural language processing / other artificial intelligence-based mechanisms to auto-generate smart textual narratives that summarizes the key highlights of the data. Though this approach works, it requires a significant number of resources and hard-to-find skills which is outside the bounds of a normal end-user who may want to use a reporting tool in a self-service manner and build a dashboard.

Modern reporting solutions like Tableau, AWS QuickSight, Microsoft Power BI, and others in similar league have been offering a feature to generate key insights using built-in AI/ML in the reporting tool which enables an end-user to extract insights as well as enables a report developer to have a smart visual that auto-updates the insights based on the change in the data.

In practice, this ends up being more of a fun toy than a really practical solution. Part of the issue is that decent analysis is hard, even more so when you have to develop something before even seeing the data or having any priors around feature importance.

Comments closed

Triggering a Power BI Dataset Refresh from Synapse

Published 2022-06-29 by Kevin Feasel

Nick Edwards updates a dataset:

Login to powerbi.com and in the top right hand corner locate “Settings” and then “Admin portal”

Under “Tenant settings” locate “Developer Settings” and then “Allow service principles to user Power BI APIs”.

Set this service to “Enabled” using the toggle. Next under the heading “Apply to:” select “Specific security groups (Recommended)”. Next add the newly created security group “AzureSynapsePowerBIIntegration” and click apply.

Click through for the full process.

Comments closed