Press "Enter" to skip to content

Category: Power BI

Converting an Excel Crosstab to Power BI Tabular Data

Published 2023-06-19 by Kevin Feasel

Leila Etaati breaks out Power Query:

There is a common way of seeing data in Excel as a Matrix format; many data entries look like a matrix in many contexts, from accounting to health data. However, to better analyze data and add more calculations and charts and, in general, get more insight from data, we need to change the matrix data in Excel to a tabular format in Power BI.

For example, below table, we have data about the Deaths from Cancer of Lip, oral cavity, and pharynx by age, sex, and year.

Not surprisingly, Power Query does a really good job at handling this common Excel style.

Comments closed

The Importance of the New Power BI Project

Published 2023-06-19 by Kevin Feasel

Paul Turley lays out an explanation:

Back in December, I posted this short article about how Power BI can participate in Continuous Integration & Continuous Deployment (CI/CD), from simplified code sharing and version control all the way up to fully-automated Build automation & deployment scenarios. However, since the product inception, there have been significant challenges in orchestrating versioning and code sharing with a single PBIX file. Achieving DevOps and CI/CD was possible but it required creative work-arounds and manual steps. In April of this year, Nathan Hales contributed this post, showing how to use Tabular Editor to manually decompose a PBIX file into a Model.bim and then create a Git repo and Azure DevOps project.

Although the essential pattern is the same, the need for manual steps and work-arounds is eliminated with this new feature release!

This is really good news, and Paul shows you how to enable the feature (currently in preview) and use it.

Comments closed

Creating a Sparkline in the New Power BI Card Visual

Published 2023-06-19 by Kevin Feasel

Erik Svensen tries out an updated visual:

In the June 23 release of Power BI desktop we has been blessed with a new card visual that not only will give a better performance compared to adding multiple cards in the past but it also gives us some formatting options we have been dreaming of for a long time.

Here is a link to the official blogpost – New card visual | Public preview | Microsoft Power BI Blog | Microsoft Power BI

On social media you have probably already seen a lot of examples of usages, and I wanted to share how you can add a sparkline to the card to make it look something like this.

Erik does a good job of showing off the capabilities of this updated card visual.

Comments closed

Drillthrough Support in Power BI Paginated Reports

Published 2023-06-16 by Kevin Feasel

Dan English is excited:

Last month there was a new update that has been made that provides support for drillthrough report actions in the Power BI service (Microsoft Fabric)! Those of you who are familiar with SQL Server Reporting Services (SSRS) know what a great feature this is to include in your reports to allow users to jump to another report for additional information such as detail level report and even allow the ability to pass the context (parameters) to the report.

Drillthrough was a big item that might have been a blocker for some companies preventing them from migrating from on-prem SSRS or Power BI Report Server (PBIRS) to the Power BI, well not anymore:)

So how does this work? 

Read on to learn more, including something which is in Power BI paginated reporting but not SSRS.

Comments closed

Changing Visual Titles on Filter with DAX

Published 2023-06-16 by Kevin Feasel

Allison Kennedy has a new title:

Today’s post is a short one with a video to demonstrate what I have learned. I’m a big believer in providing your end user with as much information as possible. Someone new should be able to pick up the report and understand it without needing to get a degree in Power BI or go digging through your company’s intranet to learn what’s what. 

One of the neat features that Power BI provides, is the ability for any visual to act as a filter or ‘cross-filter’ to other visuals. This provides us with comparative values and highlighted bars, but Power BI tooltip just says ‘highlighted’, which isn’t very helpful.

Click through for a bit of DAX code which can help with that, as well as a video demonstrating how it all comes together.

Comments closed

DirectQuery Data Modeling

Published 2023-06-14 by Kevin Feasel

Jason Cockington share some advice:

From my experience, most people who have reports built on a DirectQuery connection into their data source did so because of a lack of understanding of what the DirectQuery connection was designed to achieve.  For the vast majority of reports, Import mode is the best solution for working with data in Power BI.  DirectQuery should really only ever be applied when you are trying to solve one of the following challenges.

  1. Real-time Data – you need to see the latest available data from the source
  2. Huge Datasets – you have many billions of rows of data (more than 10Gb) so you just can’t import it into Power BI
  3. Regulatory Compliance – the data must stay in the source for data security/privacy reasons

Click through for more information.

Comments closed

Calculating Moving Averages with DAX

Published 2023-06-13 by Kevin Feasel

Leila Etaati smooths the curve:

The calculation will be done for all rows, till we don’t have the anymore data ahead ( 3 rows ahead) as you can see it will finished on row 2018 as 2018 is the average of 2018, 2019 and 2020.

so in the moving average we will miss some data point but replacing with average of data.

Less data point but help us to see a clear trend.

Click through to see how to do this. Moving averages is really good for the reports in which you want to get a rough magnitude but don’t care about exact values and don’t want oscillations to throw you off. For example, if I need to know how many seats to reserve for an event, I might use a moving average of the last several events as my baseline, rather than the prior event’s number.

Comments closed

Power BI Theme Generator Updates

Published 2023-06-12 by Kevin Feasel

Seth Bauer has some updates for us:

Gradients add depth and visual interest to your reports and dashboards. With our enhanced gradient selections, you can now choose from a wide array of gradient styles to create eye-catching visualizations. Whether you prefer subtle transitions or bold color shifts, the Power BI Tips Theme Generator has you covered. Unleash your creativity and elevate your designs with stunning gradient effects.

Not all of the updates are in the free plan, though some of them are.

Comments closed

Contrasting Lakehouse, Warehouse, and Datamart in Fabric & Power BI

Published 2023-06-12 by Kevin Feasel

Reza Rad disambiguates three terms:

Three types of objects in the Microsoft Fabric have similarities in what they can do for an analytics system. These three are; Lakehouse, Data Warehouse, and Power BI Datamart. All three objects provide storage for the data, which can be loaded into them using an ETL process and read using something like a Power BI report. In this article and video, I’ll explain the actual differences and how to choose the best option for your implementation and architecture.

Reza does a good job explaining when each of the three fit in and even has a nice chart to work out which one you might want to use.

Comments closed

What’s New in Power BI: May 2023 Edition

Published 2023-06-09 by Kevin Feasel

Ronald Kraijesteijn talks about some of the more interesting updates:

In May 2023, Microsoft announced a series of updates and new features to its Power BI platform, aiming to improve user experience, accessibility, and performance. As a skilled blog writer, I will provide you with a comprehensive summary of these updates, covering all areas from reporting and modeling to embedded capabilities and accessibility improvements.

There’s a lot on this list to check out.

Comments closed