Category: Power BI

Chris Webb uses dynamic M parameters:

My favourite – and it seems many other people’s favourite – new feature in the February 2022 Power BI Desktop release is support for more datasources (including SQL Server, Azure SQL DB and Synapse) with dynamic M parameters. In my opinion dynamic M parameters are extremely important for anyone planning to use DirectQuery: they give you a lot more control over the SQL that is generated by Power BI and therefore give you a lot more control over query performance.

Teo Lachev has already stolen my thunder and blogged about how the new functionality allows you to use a TSQL stored procedure as the source of a table in DirectQuery mode. In this post I’m going to show you something very similar – but instead of using a stored procedure, I’m going to show a simple example of how to use a TSQL table-valued function, which I think has a slight advantage in terms of ease-of-use.

Leaving aside thoughts on table-valued functions in general, dynamic M parameters looks like a really nice feature and as Chris notes, it also works for things like stored procedures.

Gogula Aryalingam takes us through Power BI goals:

The feature is currently in preview, introduced some 8 months ago, and has quite a lot of promise. For me, it is particularly exciting since I am working with a large customer, who is a perfect candidate to implement goals for. So, what is Goals in Power BI?

Let us take a quick scenario first: Organizations, regularly (if not frequently) monitor indicators of their business performance to ensure their goals and aspirations are met. Sometimes these aspirations are difficult to keep track of due to various complexities. Consider a goal called Reduce employee turnover and increase satisfaction (something that I picked up from here). To effectively understand and track its progress, the organization would probably have a few key performance indicators (KPIs) that make it easy to look at reducing employee turnover and increasing satisfaction objectively. One such KPI could be a low human capital Turnover Rate while another could be a high Employee Satisfaction Indicator. Collectively these KPIs will help determine the achievement of the goal within a stipulated period (such as a calendar year). Similarly an organization will have many goals that are aligned to organizational KPIs or metrics. Sometimes, certain KPIs/metrics may cascade down the organization’s departments, where each department’s performance determine the overall organizational performance.

Read on to see how Goals work and one use case involving KPIs.

Imke Feldmann has the need for speed:

In this article I’m going to present a method about transforming multiple columns at once in a fast way. This method also allows you to reference columns that exist in your table already. As I have described in a previous article, this cannot be done using the native Table.TransformColumns function that will be applied if you do column transformations using the UI in Power Query. The function I am sharing here allows you to enter a list of column names to be transformed and a function that defines the transformation itself. So you have to be familiar with defining custom functions to use this approach.

Click through for Imke’s function and explanation but also check out the comments for another take on the problem.

Melissa Coates has a plan:

I’m really excited to share with you that we’re working on a new set of Power BI guidance called “Power BI Implementation Planning.”

The first pieces of content are some of the most common Power BI usage scenarios. In terms of scope of content, we’re just getting started – there will be LOTS more content to come. We’ll be iteratively publishing additions to this set of content over the next several months.

There are a lot of smart people working on this project.

Soheil Bakhshi starts off a series on incremental refresh in Power BI:

Incremental refresh, or in short, IR, refers to loading the data incrementally, which has been around in the world of ETL for data warehousing for a long time. Let us discuss incremental refresh (or incremental data loading) in a simple language to better understand how it works.

Read on for the explanation as well as how you can implement it in Power BI Desktop. There are a lot of instructions here but they include publication and testing as well as development.

Paul Turley has a list:

When you sign-up for the Power BI service at PowerBI.com (this address redirects to App.PowerBI.com), use establish a tenant for your organization, hosted in the Azure cloud. Even if you setup a 90-day trial account, you have a tenant that you can upgrade later on. By default, all of the datasets, reports and other assets you publish to the workspaces in your tenant, are in a shared capacity. This means that Microsoft will manage the storage and resource allocation among shared servers in their data centers. Larger organizations will benefit from a dedicated capacity “Premium” tenant. This means that Microsoft dedicates at least one virtual machine in the Azure data center, with guaranteed capacity, for your tenant. With Premium-per-User (PPU) licensing, you pay per user.

Click through for a list of available options and some of the implications of each.

Paul Turley explains when to use each type of report:

Technology evolves, the industry changes and the way businesses use technology changes. The road that has brought us to the current state of reporting capabilities in the Microsoft data platform has been long and winding. Ten years ago, we were using SQL Server Reporting Services (SSRS) to create interactive, dashboard-like reports scorecard reports. It wasn’t easy and the report interactivity wasn’t exactly “snappy” as the report server had to re-query data and re-render the report every time a link or chart series was clicked. But, because SSRS was the best tool that we had back in the day, the techniques we used for advanced report development were arcane by today’s standards. Over the years, I’ve written a few articles and posts about how to create reports with interactive KPI gauges, charts and scorecards using SSRS. Today we have a better tool for that style of reporting and analytics. Reporting Services remains a powerful and flexible tool for a few different styles of reports. Now that SSRS has been integrated into the premium Power BI service as “Paginated Reports”, this raises questions about how and when to use each tool – and how to use them together.

Read on for more information, including a handy set of bullet points to help you make a decision.