Press "Enter" to skip to content

Category: Power BI

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

The Importance of Data Shaping

Published 2022-09-01 by Kevin Feasel

Paul Turley shapes the youth of data:

Power BI is a new tool and dimensional modeling is an old idea. One of the challenges is that, like other modern self-service analytics products on the market, Power BI doesn’t force self-service data jockeys to transform their data before reporting with it. If you want to import a big, wide spreadsheet full of numbers and create charts in a Power BI report, knock yourself out. But, the solution won’t scale and you will inevitably run into walls when you try to make future enhancements. Similar problems arise from importing many tables from different sources and transactional systems. Several tables all chained together with creative mashups and relationships present their own set of problems. The first iteration of such an effort is usually a valuable discovery method and learning experience. Great… treat it as such; take notes, make note of the good parts and then throw it away and start over! In Fredrick Brooks’ “The Mythical Man Month“, he cites that for most engineering projects, the first six attempts should be abandoned before the team will be prepared to start over and complete the work successfully. He was a chemical engineer before working for IBM; and hopefully, our methods in the data engineering business are more effective then his 6-to-1 rule. But, this makes the case the prototypes and proof-of-concept projects are a critical part of the learning path.

The tools don’t make the rules.

Unless you’re talking about the lambda architecture, in which case that’s kind of accurate. But we’re not talking about that here.

Comments closed

Extracting Multiple Pages from a Website in Power Query

Published 2022-09-01 by Kevin Feasel

Matt Allington has a new project:

Every now and then when I have a Power BI project of interest to me, I like to create a video of the end to end process of building a new report. This allows me to share some “warts and all” real-world examples of how to go about building a Power BI report. It gives me a chance to show some concepts (such as creating functions and extracting multiple pages from websites) but also to show that these things are seldom smooth and error free.

Click through for a video demonstration of website data extraction and combination in a Power BI report.

Comments closed