Press "Enter" to skip to content

Category: Power BI

Showing Filter Selections on Power BI Reports

Published 2022-08-03 by Kevin Feasel

Mara Pereira makes a Power BI breadcrumb:

When I’m developing reports, I’m asked multiple times to “hide” the slicers/filters from the report page. Usually this is to make space in the page for other visuals and because customers don’t want to use the filter pane for some reason.

This happened so many times, and only in the last couple of months I decided to try some things out and get a bit creative with Power BI.

I came up with two solutions, which I think work great in these scenarios.

Read on to see the solutions.

Comments closed

Power BI CAT Guidance

Published 2022-08-03 by Kevin Feasel

Matthew Roche advises customers:

I’ve written previously about some of what the Power BI CAT team does, but the Power BI guidance documentation only gets a passing mention… and it’s worth going into more deeply.

A lot of what the Power BI CAT team does involves working with large enterprise customers. These customers are often trying to achieve difficult goals that often involve complex data architectures, and Power BI is often a significant part of their end-to-end information supply chain. We get involved when these enterprise customers need help achieving their strategic goals, and this help often includes helping them effectively use the existing capabilities of Power BI.

Read on to learn more about this guidance documentation.

Comments closed

Variables in DAX

Published 2022-08-03 by Kevin Feasel

Marco Russo and Alberto Ferrari show us how to use variables in DAX:

Variables were introduced in DAX in 2015 and so far, they have proven to be the best enhancement of the DAX language ever. When presented with the concept of variables, most newbies focus on performance improvement, thinking that you introduce variables in your code mainly to obtain better performance. Although variables can improve performance, performance is a minor advantage. There are several more important considerations that should encourage any DAX developer to make extensive use of variables. In this article we share a few considerations, along with best practices about variables and DAX.

Read on to see what makes variables so powerful.

Comments closed

An Overview of Power BI Datamarts

Published 2022-08-02 by Kevin Feasel

Melissa Coates has a new diagram for us:

An new diagram of Power BI Datamarts is now available. It includes the technical components of a Power BI datamart.

Want to download a copy? Head on over to the Diagrams page where the latest version will always be. You can download a PDF copy of it from the Diagrams page.

This blog post includes a brief summary of each technical component of a Power BI datamart. Datamarts are really new, and they’ll be changing as (1) the functionality matures over time, and (2) based on feedback that customers give. Therefore, I’ve kept the descriptions below brief.

Despite their brevity, the descriptions are worth the read.

Comments closed

Detecting Data Changes in Power BI Incremental Refresh

Published 2022-08-01 by Kevin Feasel

Chris Webb writes some M:

One feature of Power BI incremental refresh I’ve always been meaning to test out is the ability to create your own M queries to work with the “detect data changes” feature, and last week I finally had the chance to do it. The documentation is reasonably detailed but I thought it would be a good idea to show a worked example of how to use it to get direct control over what data is refreshed during an incremental refresh.

Read on to see how it works, including a couple gotchas around things like the shape of query results.

Comments closed

Data Quality Checks in Power BI

Published 2022-07-28 by Kevin Feasel

Kristyna Hughes wants to match up data:

Picture this, you have a report in Power BI that someone passes off to you for data quality checks. There are a few ways to make sure your measures match what is in the source data system, but for this demo we are going to use python and excel to perform our data quality checks in one batch. In order to do that, we are going to build a python script that can run Power BI REST APIs, connect to a SQL Server, and connect to Excel to grab the formulas and to push back the quality check into Excel for final review. To find a sample Excel and the final python script, please refer to my GitHub.

Check out the GitHub repo as well as Kristyna’s very detailed walkthrough.

Comments closed

Target Areas on a Line Chart

Published 2022-07-28 by Kevin Feasel

Mara Pereira adds target bands to a line chart:

At the time I could not really find an easy way to achieve this… Until error bars came out!

Don’t be fooled though, it’s still a bit tricky to build a line chart like this, however I found it way easier now than before.

So, you must be thinking now “how did you do that?”.

Well, let’s find out!

The end result looks really nice, though it takes a lot of work to get there.

Comments closed

Power BI and Synapse Book Roundup

Published 2022-07-25 by Kevin Feasel

Chris Webb checks out some books:

I like free stuff and I like books, so of course I like free books – and it seems that the more I provide free publicity for relevant books here the more free books I get sent. I’ve now got enough to merit writing another post covering those I’ve received recently from various publishers and authors. As always these are not reviews, just short summaries of books you might want to check out.

Read on for the not-reviews.

Comments closed

Replacing Error Values on All Columns in Power BI

Published 2022-07-22 by Kevin Feasel

Kristyna Hughes needs to clear out some errors:

End users that are not trained in data governance but are actively involved in maintaining a data set can easily make data entry mistakes or create inconsistent data types within columns. For example, you may have a column in the dataset called “Sales” and instead of 0, someone may type “None” or “NA”. When this gets loaded into Power BI, Power BI will not know how to convert the text value “None” to a number, and it will throw an error on the refresh of the report.

One way to mitigate the impact of user-entered data is to replace errors with null values. This is not ideal since it doesn’t fix the data entry issues, but it does enable reports to still be refreshed and used while the data issues are addressed. In Power Query, you can manually replace the errors with null by going to the “Transform” tab then selecting the drop down for “Replace Values” and choosing “Replace Errors”.

Click through for a script which does this for all columns in Power Query.

Comments closed

The Basics of Slowly Changing Dimensions

Published 2022-07-21 by Kevin Feasel

Soheil Bakhshi explains what slowly changing dimensions are:

Another example is when a customer’s address changes in a sales system. Again, the customer is the same, but their address is now different. From a data warehousing standpoint, we have different options to deal with the data depending on the business requirements, leading us to different types of SDCs. It is crucial to note that the data changes in the transactional source systems (in our examples, the HR system or a sales system). We move and transform the data from the transactional systems via extract, transform, and load (ETL) processes and land it in a data warehouse, where the SCD concept kicks in. SCD is about how changes in the source systems reflect the data in the data warehouse. These kinds of changes in the source system do not happen very often hence the term slowly changing. Many SCD types have been developed over the years, which is out of the scope of this post, but for your reference, we cover the first three types as follows.

Click through for depictions of the first three types as well as implementation details and pains.

Comments closed