Press "Enter" to skip to content

Category: Power BI

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

Thoughts on Power BI Datamarts

Published 2022-09-14 by Kevin Feasel

Marc Lelijveld dives into the topic of Datamarts in Power BI:

The first post after my vacation. I was in doubt for a while whether I should write this one or not… given the preview state. Since one of the customers I work for asked me for advise today about Power BI Datamarts, I thought lets do it! They convinced me that it would be a valuable blog for many. I hope for you too.

What is this blog about? I will explain my first experiences with Power BI Datamarts which is currently in preview state. I will elaborate on my test cases, the value I see in Datamarts but also some open ends and where you can potentially position it in your Power BI solution architecture.

I think Marc ends up pretty far on the positive side of the opinion curve here relative to many early reviews.

Comments closed

ALLSELECTED in DAX

Published 2022-09-13 by Kevin Feasel

Marco Russo and Alberto Ferrari explain how the ALLSELECTED function works:

There are two ways to describe what ALLSELECTED performs and what its use cases are: a simple way and a complex way. In this article, we focus on a simple description of the function and its main use cases. We also provide details about when and how you can use ALLSELECTED without having to worry about the intricate details. If and when you want to go to the next level, invest the proper amount of time required to study this article: The definitive guide to ALLSELECTED. Be careful, the details are really complex and if you stick to the best practices, you can safely ignore said details. A real DAX guru should know all the details, but in this article we focus more on an introduction to ALLSELECTED.

Because of its inherent complexity, we describe ALLSELECTED by first focusing on a business case where ALLSELECTED is a good fit. We then provide an imperfect – yet useful – description of what ALLSELECTED is performing. And finally we provide the few best practices to follow when using ALLSELECTED. We will not provide the complete description of ALLSELECTED in terms of shadow filter context, not even as an introduction.

Even the simple solution is fairly complex.

Comments closed

Cancelling a Power BI Dataset Refresh

Published 2022-09-12 by Kevin Feasel

Chris Webb says, never mind:

The thing that got me excited about the Power BI Enhanced Refresh API, and which inspired me to start this series of posts, was the fact that for the first time it gives you a way of cancelling Power BI dataset refreshes. In this last post in the series I’ll show you how you can add an Action to your Power Automate custom connector to cancel a refresh and how you can use it in a Power Automate Flow.

Click through to see how.

Comments closed

Creating a Date Column in Power Query from Year Number and Month Name

Published 2022-09-12 by Kevin Feasel

Erik Svensen needs to build a date:

Sometimes it’s the little things that can help you minimize the number of steps in your Power Queries.

Here is another example that might help you.

Let’s imagine your data contains two columns with year and the month name and you want to create a date column.

Read on for a clever function modification to generate a date from these two fields.

Comments closed

EvaluateAndLog Not Executed Due to Optimization

Published 2022-09-07 by Kevin Feasel

Jeffrey Wang wraps up a series on the EvaluateAndLog function:

This is the fourth and the last article on the EvaluateAndLog function. While DAX developers were excited about the prospect of printing the values of a portion of a DAX expression, many of the early adopters of the function were puzzled by a mysterious message they ran into from time to time: “EvaluateAndLog function is not executed due to optimization.” Another question on people’s mind is “While it’s great to be able to see the values of a sub-expression used in my calculation, is there a way for me to see the values of other expressions which are not needed to calculate the final result?” I will provide answers to both questions in this post.

Click through to understand why you might get this message and what it means.

Comments closed

Finding Users in Power BI Workspaces

Published 2022-09-07 by Kevin Feasel

Tomaz Kastrun has a script for us:

Determining and checking who has access to a particular workspace in your organisation can be time consuming task. You can always retrieve the list of workspaces and access the list of all users with PowerShell cmdlet Get-PowerBIWorkspace.

The results of this cmdlet gives you a great way to get to valuable information easy and fast. And the following script can do just that.

Click through for the script, which also excludes personal workspaces. Tomaz then provides a bonus script creating a graph of how users and workspaces connect.

Comments closed

Refreshing Power BI Datasets from SSIS

Published 2022-09-07 by Kevin Feasel

Teo Lachev solves a problem:

Scenario: You use SSIS to load data for on-prem BI solution. As a last step of the ETL pipeline, you want to refresh a Power BI dataset. There’s quite a bit of misinformation on the Internet about how to do this, hence this blog.

Read on to see how you can do this, supposing that you’re hosting in a Premium-Per-User or Premium workspace.

Comments closed

Getting a Power BI Dataset’s Refresh History

Published 2022-09-06 by Kevin Feasel

Chris Webb wants to know the lore:

In the last post in this series I showed how to get the status of an individual Power BI dataset refresh. In this post I’ll show how to get the history of a dataset’s refreshes and save it to a file in OneDrive for Business – useful if you want to do some analysis on refresh performance or the causes of failures.

This is one of the most straightforward things to do with the Power BI Enhanced Refresh API , especially once you’ve already got a custom connector created in the way I’ve shown in the previous posts in this series. You just need to add a new Action to your custom connector, fill in the General section:

Click through to see what to do.

Comments closed

Power BI: Merge Joins and Nullable Columns

Published 2022-09-06 by Kevin Feasel

Chris Bailiss dives into some join problems in Power BI against Snowflake and SQL Server:

Power BI generates SQL that performs very poorly against Snowflake (and possibly other database platforms) when joining nullable columns, e.g. in the “Merge Queries” action in the Power BI query editor.

Specifically, the SQL generated prevents the use of the “hash join” physical join algorithm that has been one of the main algorithms used for decades by database engines when executing joins between large tables. This forces database engines to fall-back to using very inefficient physical joins (e.g. loop/cartesian join) that can perform orders of magnitude slower.

The reason why Power BI generates different SQL for non-nullable for nullable columns in a join is explained in the “Problem Part 2” section below.

Read the whole thing—and there’s a lot of good information in there.

Comments closed