Press "Enter" to skip to content

Category: Power BI

Always Use Explicit Measures in Power BI

Published 2023-10-05 by Kevin Feasel

Ed Hansberry has some advice for us:

All of your analytics in Power BI are done with measures, and there are two ways to create measures. It is important to understand the difference between the two methods. If you drag a field from the data pane directly into a visual, that is an implicit measure. The model usually wraps it with SUM(), but other aggregations are possible, like COUNT() or AVERAGE().

Read on to see what’s wrong with using implicit measures, as well as how to make these explicit.

Comments closed

Distinct Counts in Power BI and KQL

Published 2023-10-04 by Kevin Feasel

Dany Hoter needs a distinct count:

Calculating distinct counts on massive distributed datasets is not trivial.

Kusto (aka Azure Data Explorer/KQL database in Fabric) dcount and dcountif functions use a special algorithm to return an estimate of distinct counts

The new functions count_distinct and count_distinctif were recently added to calculate exact distinct counts. These two functions are much more expensive than the original ones.

Read on for more details on how this all works.

Comments closed

Automated Power BI Visual Testing with PBI Inspector

Published 2023-10-02 by Kevin Feasel

Chris Webb phones a friend:

This week, one of my colleagues at Microsoft, Nat Van Gulck, showed me a cool new open-source tool he’s been working on to make VisOps for Power BI much easier: PBI Inspector. What is VisOps? I’ll admit I didn’t really know either, so being lazy I asked Nat to write a few paragraphs describing the project and why it will be useful:

Read on for Nat’s description and an example of PBI Inspector in action.

Comments closed

Maintaining Existing Power BI Data while Loading More with Fabric

Published 2023-09-25 by Kevin Feasel

Chris Webb looks back on an older post:

To be honest I’m slightly ashamed of this fact because, as I say in the post, the solution I describe is a bit of a hack – but at the same time, the post is popular because a lot of people have the problem of needing to add new data to the data that’s already there in their Power BI dataset and there’s no obvious way of doing that. As I also say in that post, the best solution is to stage the data in a relational database or some other store outside Power BI so you have a copy of all the data if you ever need to do a full refresh of your Power BI dataset.

Why revisit this subject? Well, with Fabric it’s now much easier for you as a Power BI developer to build that place to store a full copy of your data outside your Power BI dataset and solve this problem properly.

Read on for an example of the new solution.

Comments closed

Dynamic Highlighting of Data Points Based on Slicer Selection

Published 2023-09-25 by Kevin Feasel

Nikola Ilic shines a light on the data:

To quickly explain: when a user selects, for example, Contoso in the slicer, the Contoso bar should be highlighted by using a different color. As much as this sounds like a very basic and common business request, there is no straightforward solution in Power BI (or, at least, I’m not aware of it:))

However, the client’s wish is (almost) always our command – so, let’s see how this feature can be implemented with a little bit of data model tweaking and leveraging some DAX code.

Spoilers: there is a solution, though it does involve quite a few steps.

Comments closed

Using DAX to Find Products Missing Sales

Published 2023-09-25 by Kevin Feasel

Marco Russo and Alberto Ferrari observe the dog that didn’t bark:

What products did not sell in a specific area, store, or time period? This may be an important analysis for several businesses. There are multiple ways to obtain the desired result. Some specific implementations might be needed because of the user or model requirements, whereas developers can choose any formula in several cases. Or you might just find a solution on the web and blindly implement it without questioning whether there is a better way to achieve what you want.

It turns out that different formulas perform very differently. Choosing the right one in your scenario can make a slow report fast. This article analyzes the performance of different formulations of one same algorithm. 

It’s interesting to see the performance profile here: most are reasonably close together, although you can still get a 2x gain from using the fastest approach versus the second-slowest. And then there’s the slowpoke.

Comments closed

Power BI Shareable Cloud Connections and Multiple Connections

Published 2023-09-19 by Kevin Feasel

Chris Webb shows off an interesting aspect of a new feature:

A few weeks ago an important new feature for managing connections to data sources in the Power BI Service was released: Shareable Cloud Connections. You can read the blog post announcing them here. I won’t describe their functionality because the post already does that perfectly well; I want to focus on one thing in particular that is important for anyone using Power BI with Snowflake (and, I believe BigQuery and probably several other non-Microsoft sources): Shareable Cloud Connections allow you to have multiple connections to the same data source in the Power BI Service, each using different credentials.

Read on to see what Chris means and how you can take advantage of it.

Comments closed

Adding a Service Principal to a Fabric Workspace via API

Published 2023-09-14 by Kevin Feasel

Marc Lelijveld tackles a challenge:

Lately, I found myself struggling using the Power BI REST API to add a service principal to the (Fabric) workspace. After I engaged with some other folks, I managed to succeed. In this blog I will elaborate on the mistake I made and how I got it to work. For some this might be a less useful blog. Though, I still wanted to blog this even if it is for my own memory on how to do this.

Read on for the story.

Comments closed