Press "Enter" to skip to content

Category: Power BI

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

Reading Delta Tables from Power BI via Synapse Serverless

Published 2022-10-10 by Kevin Feasel

Dan English is up for a data lake change:

In this post I just wanted to show the ability to use the Delta Lake format that is very common now with Power BI. I will go over a quick example of creating the files to reference, building a view to use with Power BI, and then querying the data in Power BI.

In my Synapse Workspace I created a Linked service connection to an Azure SQL Database that has the AdventureWorksLT database loaded which is the sample database you can create when you first create a SQL instance in Azure and here is a walkthrough link and see the Additional settings section.

Dan shows how to create the lake files in delta format via Synapse pipeline and then how to query the data from there.

Comments closed

Tracking Power BI Desktop Activity in SQL Server

Published 2022-10-06 by Kevin Feasel

Chris Webb looks in on things:

Something I do all the time when performance tuning Power BI is use SQL Server Profiler to monitor query and refresh activity. There’s a handy external tool that lets you open up Profiler with a connection to Power BI Desktop; if you’re using Power BI Premium you can also connect Profiler up to a published dataset using the XMLA Endpoint. Profiler is a bit old-school though and likely to lead to snarky comments from SQL Server DBAs (it’s not deprecated for Analysis Services though!) who’ll say you should be using Extended Events (aka xEvents) instead. And guess what – did you know you can use Analysis Services xEvents to monitor activity in Power BI Desktop?

It’s the power of Extended Events.

Comments closed

Incremental Refresh in Power BI

Published 2022-10-05 by Kevin Feasel

Reza Rad doesn’t have time to wait for a full dataset reload:

The default configuration for the Power BI dataset is to wipe out the entire data and reload it again. This can be a long process if you have a big dataset. Hybrid tables in Power BI keep part of the data in DirectQuery, and the rest is imported for data freshness and performance. In this article, I explain how you can set up an incremental refresh in Power BI and its requirements. You will also learn about Hybrid tables in Power BI. Incremental Refresh is not just in Power BI datasets but also in Dataflows and Datamarts. In this article, you learn to load only part of the changed data instead of loading the entire data each time. To learn more about Power BI, read the Power BI book from Rookie to Rock Star.

Click through for the article.

Comments closed