Press "Enter" to skip to content

Category: Power BI

Preventing Calendar Overrun in Power BI

Matt Allington updates an older article:

Consider the example below where the CalendarYear is filtered for 2019 and the values of the measures Total Sales and Total Sales YTD are displayed by month. As you can see, the total sales are shown up to July 2019. This is because with the sample data, the last sales date is somewhere in July 2019. However, the values of Total Sales YTD are repeated all the way until the end of year (July 2019 to December 2019). This is what I call Calendar Over Run. It is common to want to prevent this overrun.

Read on for two separate methods of preventing this visual issue.

Comments closed

Counts of Last-Known States of Items with DAX

Phil Seamark has an interesting problem:

The requirement was simple enough. Take the following dataset and, for any given day, produce a count of each possible State using the last known State for any given TestID. The dataset contains six unique Test IDs (A through F). At any given point in time, we first want to establish the last State for each TestID. We also want to group this by day and produce a count value for each possible State. Note, a given TestID can have more than one event in a day, and we only care about the last one.

I’m particularly interested in this because I find a lot of merit in the event-based structure in Phil’s input dataset, but it can be tricky going from that to data in a shape the customer likes.

Comments closed

An Introduction to Power BI Goals

Imran Burki brings us an introduction to Power BI Goals:

One of the things I love about Power BI (and Microsoft in general) is that they empower everyone in the organization to utilize their software – Power BI Goals are so easy to set up. There’s absolutely no special skillset required. You just need a Power BI Premium or Premium Per User license. Power BI Goals essentially enable you to keep track of key performance indicators in a single, unified view. Goals, and the actuals, are data driven. Goals can also be hardcoded. The data for actuals and goals must reside in a report that you can access.

Click through for an example as well.

Comments closed

Showing Ranges of Data with a Single Slicer

Marco Russo and Alberto Ferrari have another good use case for calculation groups:

Because the axis of the visual must show months outside of the slicer selection, we cannot use the usual Date[Calendar Year Month] column. Indeed, if we put the Date[Calendar Year Month] column on the X-axis, the only visible month would be the selected month. It is worth remembering that the selection of the values to show on the axis is independent from the measure. If a slicer is filtering one month, there is no way to show additional months from the same table on either the rows or the columns (or the X-axis, as in the example).

Therefore, we must create a separate table that is not subject to filtering from the slicer. This way, columns from that table show all the rows, and we can control their visibility through a measure. Once the new table is in place, we write a measure that produces a value for only the last six months out of all the months visible, and leaves the remaining months blank in order to hide them.

Read on to see how.

Comments closed

Checking Out Power BI Goals

Treb Gatte takes a step back:

In part 1, I gave you an introduction to Power BI Goals and provided some context on what information could be tracked with the feature. I received some feedback that it would be great to do an “unboxing” post, to get a look at the feature. Thus, this post will be that. Part 3 will be a Power BI Goals 101 post where I step you through the process.

Power BI Goals requires you to either have a Power BI Premium per user license or to have access to Power BI Premium and have a Power BI Pro license assigned to you. If you do, then you will notice a new trophy icon when you go to PowerBI.com.

Click through for the overview.

Comments closed

From Azure Analysis Services to Power BI Premium Per User

Gilbert Quevauvilliers picks back up on a new series:

Welcome to the first in my blog post series on evaluating the different aspects when looking to migrate from Azure Analysis Services (AAS) to Power BI Premium Per User (PPU).

Apologies for this taking a few extra weeks to get started, life has been super busy, but as they say “Better late than never”.

In this post I am going to compare the Query Performance of an AAS Cube compared to a PPU Cube.

Click through to see how Power BI Premium Per User stacks up against Azure Analysis Services.

Comments closed

Power Query Data Profiling

Ed Hansberry takes us through the data profiler in Power Query:

A solid green bar is usually best. It means there are no issues in that column, as shown in the Discounts field. On the far right in the COGS field, there is a tiny bit of gray. That means there is no data, or a null, in one or more records. The bigger the gray bar, the higher percentage of nulls there are. In the middle though we have a problem. Anything in red is an error and must be fixed. These may cause more errors in further transformations downstream, and will usually cause refresh errors.

Before we address this, let’s get a bit more information about our data. Go to the View ribbon, and there are 3 settings we want to look at.

I really like what the data profiler provides us. If you’re a regular Power BI user, I highly recommend checking it out if you haven’t already.

Comments closed

Centralized Data Modeling via Power BI Templates

Haroon Ashraf aims to square the circle:

Data modeling is the way you can arrange and link your organizational data (typically in the form of tables) for reporting and analysis.

In other words, it is the strategy of lining tables with each other to get useful information by following the standard practices and domain knowledge of the organization.

Traditionally, it stands for implementing the star or snowflake schema from the perspective of the data warehouse BI solution.

What is Centralized Data Modeling?

Centralized data modeling means a generic data model consisting of some commonly used tables, relationships, and hierarchies that are shared across the organization. These elements the starting point for Power BI report development to anyone eligible, interested, and capable to do so.

With that in mind, read on to learn how you can use Power BI templates to bring this about. I joke about squaring the circle here because if you treat Power BI as a self-service business intelligence tool, the users may not be totally familiar with what you’re doing and could end up accidentally undermining your plans. That said, it’s a good approach to solving this common problem.

Comments closed

Visualizing Power BI Refresh in Real-Time

Phil Seamark doesn’t have time to wait:

I recently wrote an article showing how you can visualise a dataset refresh using Power BI. It was a pretty cool way to show some of the internal workings of what otherwise is a black box. The idea from my earlier article uses SSMS Profiler to run a trace against a database hosted in Azure AS, or Power BI Premium. Once the refresh is complete, you import the results of the SSMS Profiler trace into a Power BI report to analyse. The approach requires you to wait until the refresh is complete before you can start exploring the data.

Also recently, I had the opportunity work on some large models that took a long time to refresh. I wondered what might be required to update the earlier process to study the results while the refresh was underway. Does that make me too impatient? Here is what I ended up building.

Click through to enjoy the fruits of Phil’s impatience. This is quite the interesting solution, especially if you’re twiddling your thumbs and wondering if this refresh will ever wrap up.

Comments closed

Dynamic Date Formats in Power BI

Joe Billingham shows how to ensure that users see dates in the format most appropriate to them:

Which date format styles should we use if we are building a report that is being consumed internationally?

Remember, 01/12/2021 is December 1st or January 12th depending in which part of the world it is being read.

The decision may be taken from our hands if there is a company policy in place. If the company is based in the USA, for example, they may choose to use US formatted date fields as a standard for reporting across the entire business, however, if the field needs to be truly dynamic depending on the consumers location, the answer lies in this tool tip:

Click through to see how.

Comments closed