Press "Enter" to skip to content

Category: Power BI

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

Using a Tree Map as a Legend in Power BI

Published 2022-06-28 by Kevin Feasel

Prathy Kamasani makes clever use of a tree map:

I recently worked on two projects where the client wanted to show multiple metrics sliced by the same categorical data. For example, seeing how various metrics are performing over different regions or different product groups. A use case like this can be achieved in many ways; probably the best approach is to use small multiples functionality or to keep it simple, five same visuals with different metrics.

Let’s look into it with energy consumption data. Here, I want to show metrics 1 to 5 on different income levels over the years.

I like this solution when you have multiple graphs off of the same base data, like in the small multiples scenario Prathy shows us.

Comments closed

Connection Types and DirectQuery in Power BI

Published 2022-06-28 by Kevin Feasel

Reza Rad has two posts covering connection types in Power BI. First up is an overview of data importation:

Power BI is one of the BI tools in the market that supports more than one type of connection. Each connection type has pros and cons. In this section, we are going to cover everything about Import Data or Scheduled Refresh type of connection. You will learn briefly how Power BI stores data into xVelociy in-memory engine, and what are pros and cons of this method in detail.

But wait, there’s more!:

In the last post, you learned about Import Data or Scheduled Refresh as a connection type. In this post, you’ll learn about the second type of connection named; DirectQuery. This type of connection is only supported by a limited number of data sources, and mainly targets systems with a huge amount of data. DirectQuery is different from another type of connection which I’ll talk about it in the next post named Live Connection.

Understanding when (and how) to use each is important for long-term success in Power BI.

Comments closed

Report-Level Measures in Power BI

Published 2022-06-28 by Kevin Feasel

Soheil Bakhshi explains the concept of report-level measures:

Report level measures are the measures created by the report writers within a Thin Report. Hence, the report level measures are available within the hosting Thin Report only which means the report level measures are not written back to the underlying dataset and hence they are not available to any other reports.

Read on to see how you can create one. I don’t think I’d ever heard them called that before but the name makes sense.

Comments closed

Power BI Aggregations from Azure Data Explorer Data

Published 2022-06-27 by Kevin Feasel

Dany Hoter has some recommendations if you’re aggregating data from Azure Data Explorer into Power BI:

Every visual shown in a report in PBI, contains some form of aggregation

The question is how the aggregations are calculated and at which step in the pipe of bringing the data from the data source to the report.

In this article, I’ll be using data coming from Azure Data Explorer aka Kusto aka ADX.

Most of the content is relevant for other sources as well.

Read on for the advice, which I’d call fairly unexpected—I actually expected the recommendation to go the other way for performance reasons.

Comments closed

Power Query Online Memory and CPU Usage

Published 2022-06-27 by Kevin Feasel

Chris Webb wants to see how things are going:

Power Query Online is, as the name suggests, the online version of Power Query – it’s what you use when you’re developing Power BI Dataflows for example. Sometimes when you’re building a complex, slow query in the Query Editor you’ll notice a message in the status bar at the bottom of the page telling you how long the query has been running for and how much memory and CPU it’s using:

Read on to understand what the memory value indicates and for a few tips on the topic.

Comments closed

Exporting Power BI Row-Level Security Details

Published 2022-06-22 by Kevin Feasel

Gilbert Quevauvilliers needs a report, stat!:

In a previous tweet on twitter, I had elaborated on how I had extracted the RLS Roles with the details and then exported it into a CSV file which then allowed the organization to keep an audit of the RLS for the dataset.

In the steps below I will show you how I did this.

In my previous blog post I explained how to export data from a Power BI report to a CSV file here: Exporting a Power BI Visual data to a CSV File in SharePoint

Read on to see how, as well as a few notes on what it takes to get this report.

Comments closed

Operating Power BI Desktop as a B2B User

Published 2022-06-22 by Kevin Feasel

Meagan Longoria shares some notes:

I noticed Adam Saxton post a tip on the Guy in a Cube YouTube channel about publishing reports from Power BI Desktop for external users. According to Microsoft Docs (as of June 21, 2022), you can’t publish directly from Power BI Desktop to an external tenant. But Adam shows how that is now possible thanks to an update in Azure Active Directory.

Click through for the sign-in process as well as what you can do and the pitfalls you might run into along the way.

Comments closed

Delta Live Tables and Power BI Data Modeling

Published 2022-06-21 by Kevin Feasel

Tahir Fayyaz goes from Delta Lake to Power BI:

To get the optimal performance from Power BI it is recommended to use a star schema data model and to make use of user-defined aggregated tables. However, as you build out your facts, dimensions, and aggregation tables and views in Delta Lake, ready to be used by the Power BI data model, it can become complicated to manage all the pipelines, dependencies, and data quality as you need to consider the following:

– How to easily develop and manage the data model’s transformation code.

– How to run and scale data pipelines for the model as data volumes grow.

– How to keep all the Delta Lake tables updated as new data arrives.

– How to view the lineage for all tables as the model gets more complex.

– How to actively stop data quality issues that result in incorrect reports.

Read on for recommendations, a couple architectural diagrams, and some sample code.

Comments closed

Power BI Field Parameters and Type 2 SCDs with Bonus Fields

Published 2022-06-20 by Kevin Feasel

Koen Verbeeck extends the type 2 slowly changing dimension:

Power BI field parameters are a new feature in Power BI Desktop, and it’s one of the best of the past months. In short, Power BI field parameters allow you to easily switch between dimensions attributes or measures in a filter. Previously, you had to do all sorts of DAX wizardry to make this happen, but now it’s just a couple of clicks.

The goal of this blog post is not to tell you exactly how they work, but rather showcase an interesting use case. You can find more info about Power BI field parameters in the official blog post, but also here, here and here. The use case I’m talking about is slowly changing dimensions of Type 2, you know, the one where we insert a record for every change. Often, I also include an extra column for each column of which we’re tracking history: the “current value column”. For example, if we keep history of the department for an employee, I have a column “CurrentDepartment”. If a type 2 change occurs, the values of this columns are updated to the last known value for this dimension member. This allows to answer different types of questions, because sometimes users are interested in the historical values, but sometimes they just want to know the current value.

Read on for the use case as well as how you might combine field parameters with the idea of current values on type-2 slowly changing dimensions.

Comments closed