Press "Enter" to skip to content

Category: Power BI

Determining Power BI Report Fields in Use

Published 2024-12-02 by Kevin Feasel

Meagan Longoria performs a search:

Have you ever wondered where a certain field is used in a report? Or maybe you need an easy way to find broken field references in a report? Certain 3rd-party tools such as Measure Killer and Power BI Helper (not updated recently) have helped us with this task in the past. But now we can perform this task with a notebook in Fabric!

This is made possible by the Semantic Link Labs Python library. Please note that PBIR format is still in preview at the time of publishing this blog post, so use it at your own risk. Also, this works only on reports published to the Power BI service. Since this notebook is not making any changes to the report, I feel it’s pretty safe to run, but do remember that it uses CUs on your Fabric capacity while you run it.

Read on to see how it works.

Comments closed

Contrasting Power BI and Power BI Report Server

Published 2024-11-27 by Kevin Feasel

Soheil Bakhshi makes me sad:

Depending on your organisation’s preferences, data governance requirements, and the platforms you intend to use for report deployment you may use either Power BI Desktop, the “standard version”, or Power BI Desktop RS (Report Server). Power BI Desktop has variations tailored to meet specific needs, such as cloud-based analytics or on-premises reporting. While many users might only encounter the standard version, there’s another important variant for specialised scenarios.

You know the meme where the kid asks, “Mom, I want Power BI!” and mom says, “We have Power BI at home.” Power BI Report Server is Power BI at home, in all of the senses of the joke. I’ve been stuck with it at two employers and although it is better than nothing, you quickly find out how annoying it gets when that thing you know how to do in Power BI Online doesn’t work because they never supported it in PBIRS.

Comments closed

Power BI Writeback via Fabric SQL Database

Published 2024-11-26 by Kevin Feasel

Jon Voge gives us a use case for Fabric SQL Databases:

Until recently, Fabric has allowed us to choose between Lakehouses and Warehouses as a backend. For write-back use cases, neither are ideal.

  • The SQL Endpoint of Lakehouses are Read-Only, making writes from Power Apps impossible.
  • While the SQL Endpoint of Warehouses are write-enabled, they do not support enforced Primary Keys, which are a hard requirement for Power Apps to be able to write directly to a data source.

Jon briefly describes two mechanisms people used and then how you can do this more effectively with a Fabric SQL Database. Based on the article, it seems that you could probably still do the same with an Azure SQL Database, though I suppose handling the managed identity could be an issue.

Comments closed

Obtaining VisualIDs for Visuals in a Power BI Report

Published 2024-11-22 by Kevin Feasel

Sandeep Pawar checks for ID:

Log Analytics and Workspace Monitoring in Fabric logs all the activities of datasets in a workspace. These logs contain dataset, report, visual IDs which the user has to decipher to get the full picture. Dataset, report ids are straightforward but it’s not easy to get visual IDs programmatically. Chris Webb already has a blog on couple of different ways to get the visual IDs. That blog was published in 2022 and in the Fabric world we now have a couple of more options.

Read on for two additional methods you can use.

Comments closed

Custom Fonts in Power BI Reports

Published 2024-11-21 by Kevin Feasel

Elena Drakulevska opens a can of worms:

You’re presenting an amazing Power BI report filled with insights, but it feels like something’s missing. The visuals are great, but the default font? Meh. It’s like wearing flip-flops to a black-tie party.

That’s where custom fonts in Power BI come in to elevate your report and add that personal touch. Fonts do more than look good—they set the tone, show off your brand, and make your reports more engaging and easier to read. In short, they help your reports stand out.

Read on to see how, as well as important reasons why you might not want to use them.

Comments closed

Aggregate Functions in Power BI

Published 2024-11-19 by Kevin Feasel

Hristo Hristov writes some DAX:

At times when using Power BI, you want to combine your data to produce an aggregated value. The aggregation is performed over some criteria – frequently this may be time (year, month, date) or a categorical value. Some popular aggregation functions to apply can be Sum, Average, Maximum, Minimum, or Count. Typically, Power BI applies certain aggregations by default when adding data fields to visualizations. What if you wanted to create your own data aggregations? To achieve better understanding of the underlying data, how can you attain fine-grained control over the aggregations?

Read on for several DAX measures, including totals, running totals, moving averages, and day over day changes.

Comments closed

Creating a Numbers Table in Power Query

Published 2024-11-18 by Kevin Feasel

Koen Verbeeck builds a tally table:

Sometimes you need a numbers table (also called tally table) when you’re working with data. It’s a table with one single integer column that contains incremental numbers (0,1,2,3,4,…). In a database, it’s fairly easy to generate using either cross-joins or a generator function (GENERATE_SERIES in T-SQL). Recently, I needed such a table in Power BI Desktop. Suppose you don’t have a database as a source (or maybe you cannot change the database), so we need to generate this in Power Query.

Read on to see how.

Comments closed

Bullet Charts in Power BI Reports

Published 2024-11-05 by Kevin Feasel

Kurt Buhler is number one with a bullet (chart):

A report visual is useful when it displays information in a meaningful context. This context refers to other relevant data that helps someone interpret figures in a visual and use it to make decisions or take actions. The most common way to provide context is to compare actuals to a target.

There are many ways to compare actuals to a target, and in this article, we will explain in detail one way to do this by using a popular variant of the bar chart known as a bullet chart.

Kurt lays out several ways to implement a bullet chart in Power BI, so check that out.

Comments closed

Column Eviction in Power BI and Direct Lake

Published 2024-11-05 by Kevin Feasel

Paul Turley talks about fashion:

One of the core best practice guidance principals for Power BI modeling is to avoid including columns that aren’t absolutely necessary for analytic reporting. Every column uses precious memory and especially long, unique values that don’t compress very well. When consulting clients bring me large models that require expensive capacity licensing and pose report performance issues, my first inclination is to see what column data can be carved out of the model; and perhaps moved to another table for a drill-through report.

The product team came up with a very clever way to reduce the in-memory footprint of a Direct Lake semantic model: hold a popularity contest! The semantic model engine will only keep columns in memory based on their hotness. I mean this literally…

Read on to learn a bit more about the algorithm in play and how it differs from a naive Least Recently Used cache.

Comments closed

Lexing DAX with PyDAX

Published 2024-11-04 by Kevin Feasel

Sandeep Pawar reviews a DAX lexer:

The power of open-source and GenAI. Klaus Jürgen Folz recently open-sourced the PyDAX library, which parses DAX expressions to extract or remove comments, and identify referenced columns and measures. I used that library to create some demos for myself and then shared the notebook along with instructions with Replit agents to build an app for me.. 15 minutes & 3 prompts later I had a fully functional app. Give it a try : https://daxparser.replit.app/

Read on to learn more, including why I referred to PyDAX as a “lexer” and a few more notes of relevance.

Comments closed