Press "Enter" to skip to content

Category: Power BI

Tracking Lineage in Power BI

Published 2022-10-19 by Kevin Feasel

Gilbert Quevauvilliers reads the chain of custody documents:

As often happens blog posts originate from a customer requesting something I have not figured out before.

In this example the requirement was to be able to determine which data sources were being used by which tables, which were then associated to one or many Power BI datsets.

While I was working through this I figured out I could take it one step further and also if required have the actual Power Query as part of the report.

Read on to see what Gilbert came up with.

Comments closed

Finding Faulty Rows in Tabular Server Errors

Published 2022-10-18 by Kevin Feasel

Teo Lachev goes error-hunting:

A scheduled SSIS job that executes a massive DAX query to an on-prem Tabular server (Power BI can also generate this error) one day decided to throw an error “Source: “Microsoft OLE DB Provider for Analysis Services.” Hresult: 0x80004005 Description: “MdxScript(Model) (2020, 98) Calculation error in measure ‘Account Snapshot'[Average utilisation % of all CR active current accounts last 3 months]: The result of a conversion or arithmetic operation is either too large or too small.” At least we know the offending measure, but which row is causing the error? The query requests some 300+ measures for 120 million customers, so I thought someone might find the troubleshooting technique useful. Let’s ignore what the measure does for now except mentioning that it performs a division of two other measures.

Click through for the technique.

Comments closed

Creating a SQL Server Assessment Dashboard

Published 2022-10-18 by Kevin Feasel

Robert Blackburn builds a dashboard:

We must periodically evaluate the state of our databases. Luckily for SQL Server, Microsoft provides us with a customizable assessment through their SQL Assessment API Repo and API Documentation. You can change the rules per database and output the results to a database to track history.

However, that will take more than an hour. Let’s create a dashboard with the default rules in under an hour. We will use Azure Data Studio (ADS) and Power BI Desktop (PBI). If you are not familiar with them, both are free. Azure Data Studio is automatically installed with SSMS 18.7 and higher. You can also install them individually.

Read on to see how this works. Granted, it will not auto-update but unless the assessment output format changes between runs, at least you wouldn’t need to modify Power BI and could just refresh the data.

Comments closed

Dataset Changes while Deploying in Power BI

Published 2022-10-14 by Kevin Feasel

Marc Lelijveld investigates a what-if scenario:

One of the topics discussed during the session, is the effect of deployments on datasets by using native deployment pipelines in the Power BI service. Deployment Pipelines only deploy meta data from the data model, however specific changes might have an unwanted effect on the data in the dataset in the target stage.

In this blog post, I will further elaborate on several specific use cases and the effect on your dataset in the target stage.

Read on for the results of three separate tests.

Comments closed

Finding DAX Dependencies

Published 2022-10-14 by Kevin Feasel

Chris Webb tries out a DMV:

If you’re monitoring activity in Power BI, for example using Log Analytics, you’ll know that you can capture the DAX queries generated by your published Power BI reports. How do you make sense of them though? You may want to know which tables, columns or measures are being used by a query – maybe so you can work out which ones aren’t being used and can be deleted. I always thought the only way to do this would be to parse the DAX query, which would be incredibly difficult to do. Yesterday, though, Igor Cotruta pointed out on Twitter that there’s a really easy way to do this that I didn’t know about, using the DISCOVER_CALC_DEPENDENCY DMV. I’ve used this DMV before and blogged about it here, but what was new to me is that you can pass a DAX query into the QUERY restriction (it is all documented here). To see how it works let’s look at a simple example.

This looks pretty cool.

Comments closed

Power Automate Trigger on Power BI Dataset Refresh Completion

Published 2022-10-13 by Kevin Feasel

Imke Feldmann wants to know when the work is done:

If you have been working with Power BI for a while now you might have come across the standard Power BI actions in Power Automate. They allow you to automate a nice bunch of Power BI processes. But while there is a trigger that starts a flow when a Power BI dataflow action has completed, no such trigger is available for when a Power BI dataset action has (successfully) completed. So here I will present a workaround that is still possible without a premium Power Automate license.

Read on for that workaround.

Comments closed

Cross-Highlighting Power BI Charts

Published 2022-10-12 by Kevin Feasel

Marco Russo and Alberto Ferrari aren’t satisfied with a single date:

The best practice when we have multiple dates is to create a single, shared Date table and to connect it to all the date columns with different relationships. When a table has more than one date column, only one relationship can be active while the other relationships are inactive. In our sample model, the Date table connects both Sales[Order Date] and Sales[Delivery Date] with two relationships: one is active (with Order Date) and one is inactive (with Delivery Date).

Read on for a couple of options and what they mean for your visuals.

Comments closed

Auto-Aggregation on Dimension Tables: Gone

Published 2022-10-11 by Kevin Feasel

Matt Allington made the Power BI world a slightly better place:

Have you ever loaded a calendar table with lots of numeric columns (such as Year, Month Number, etc), loaded it into Power BI, and then had to manually go an turn off the default aggregation on these columns?  If you build Power BI reports, then I bet you have done this more than once. Jeffrey’s idea was that Power BI should be smart enough to detect a dimension table (one side of the relationship) and to disable these default aggregations.  There’s nothing worse than adding a Year column to a table and having Power BI add the years together. If I add a year to a table, I want to slice and dice, not SUM the years.

Matt added a request and see where that gets you.

Comments closed

Creating Power BI Scorecards from Excel

Published 2022-10-11 by Kevin Feasel

Imke Feldmann has a goal:

I like Power BI scorecards a lot, as they give a really nice interface and look to your and your teams goals. However, entering all goals manually can become a bit tedious, especially if you have your goals already sitting in an Excel file. This is often the case for example with Budget figures. In this article I will share a method using Power Automate to create a scorecard automatically from an Excel file using standard Power Automate connectors. Please note that these connectors are still a bit limited, so connections to Power BI reports for example cannot be created currently.

Click through to see how.

Comments closed

Live Connection vs DirectQuery in Power BI

Published 2022-10-10 by Kevin Feasel

Chris Webb disambiguates a couple of terms:

It’s very easy to get confused between a Live connections and DirectQuery mode in Power BI: the customers I work with frequently get them mixed up, partly because they are similar concepts, partly because other BI tools use the term “live connection” to refer to what Power BI calls DirectQuery mode. In this post I will explain the differences between these two terms.

Read on for that explanation.

Comments closed