Power BI Report Development 101 - Part 1

Updated: Feb 14

Are you new to Power BI? Maybe you've been using it for some time but you're still not sure how the end to end process works?

This flowchart was made for the users that are still in the beginning of their Power BI journey!

In my diagram, I divided the report development process into four main steps:

1) Analyse data and build your data model

2) Report Design

3) Prepare to share your report

4) Share your report

In this post, I'm going to guide you through each one of the steps, and share some useful tips and information on the way.

Curious? Check the flowchart below:

Colours legend:

Blue Squares - These steps are usually mandatory in most reporting projects

Orange Squares - These are optional steps for advanced visualisation features

Pink Squares - These are optional steps for Q&A Setup

Green Squares - These are optional steps for Row Level Security

Purple Squares - This is a mandatory step for On-Premises data sources

You probably noticed I have a small section on the right called Power BI Integrations.

I decided to add this section because I think it's important to also know what integrations with other tools are there, and how this can be useful in your use case.

I won't be talking about the Power BI Integrations in this post though. If you want to find out more about it, stay tuned!

If you've been following the latest blog posts, you probably recognised this flowchart from the Power BI Report Performance Best Practices post.

Even though the post is similar, this one will be more focused on getting started with Power BI, not on performance. However, I highly recommend you to check the Performance Best Practices post too!


1) Analyse data and build your data model

🞂 Requirements gathering:

This is one of the most important steps in any reporting project! Before you start to build anything, you need to understand your user's requirements and understand the data you will have available. A lot of times this part of this first step is neglected! You need to always have in mind that the end user wants insights, and not a "dump" of what you have in your database, try to understand him/her and the data before building anything 😉

All the other steps will be dependent on how good you understood the business user, the problem you're trying to solve and the data, so take your time.

Where to start:

Go crazy and wild and innovate with Design Thinking your Power BI reports | LinkedIn

Learning paths:

Discover data analysis - Learn | Microsoft Docs

Get started building with Power BI - Learn | Microsoft Docs

By the end of this step, you should have:

> Understood the audience that is going to be using your report to make decisions

> Understood the problem you're trying to solve

> Defined the data you're going to need to solve the problem (list of data sources)

> Understood the data you have to solve the problem

> Defined the success criteria for your project

🞂 Connect to data:

After you've done your requirements gathering, you should now be thinking about connecting to your data sources. With Power BI, you can connect to many different types of data. These sources include basic data sources, such as an Excel file or CSV. You can also connect to online services that contain all sorts of data, such as Salesforce, Microsoft Dynamics, Azure Blob Storage, and many more.

When connecting to data, it's also important to define what type of connection you're going to use. There are three types of connection modes: Import, Direct Query and Live Connection.

As a rule of thumb, you should:

> Consider using Import Mode when your dataset is less than 1GB, your data only needs to be refreshed a few times a day max, your data model is complex (lots of data transformations, lots of measures), you want the best performance

> Consider using Direct Query when you have a large dataset (>1GB), you want your data to be updated in near real time, your data is stored in a source that supports Direct Query (link), you are not planning to do complex data transformations (e.g. combining tables), you are not planning to build measures with complex logic

> Consider using Live Connection when Analysis Services is already in use, you have Analysis Services 2016+ Tabular Instances, Analysis Services instances is on the cloud, you are not planning to do transformations over the data or complex measures

Where to start:

Quickstart Connect to data in Power BI Desktop - Power BI | Microsoft Docs

What data connectors are there (aka what data sources can I connect to?)

List of all Power Query connectors | Microsoft Docs

Import vs Direct Query vs Live Connection

DirectQuery, Live Connection or Import Data? Tough Decision! - RADACAD

Learning paths:

Get data with Power BI Desktop - Learn | Microsoft Docs

Get data in Power BI - Learn | Microsoft Docs

By the end of this step, you should have:

> Defined the type of connection (Import, Direct Query, Live Connection) you're going to use for each one of your data sources, according to the business needs and the data itself

> Connected to your data sources

🞂 Transform your data:

Now that you've connected to your data sources, it's time to shape and combine your data. After all, who doesn't like to have a cleaned and shiny dataset?

In this step, we are going to shape the data to meet our reporting needs. Shaping data means transforming the data: renaming columns or tables, changing text to numbers, removing rows, setting the first row as headers, removing errors and so on.

We can also combine data. Combining data means connecting to two or more data sources (tables), shaping them as needed, then consolidating them into a single useful query. In other words, combining multiple tables together, and if you're asking yourself "What if one of my tables comes from an Excel file and another one from SQL Server??", worry not! With Power BI you can combine tables from different sources, combine files from a folder...

Where to start:

Perform common query tasks in Power BI Desktop - Power BI | Microsoft Docs

Tutorial: Shape and combine data in Power BI Desktop - Power BI | Microsoft Docs

Learning paths:

Clean, transform, and load data in Power BI - Learn | Microsoft Docs

By the end of this step, you should have:

> Your queries (tables) cleaned and ready for the modelling part (please remember to only include in your tables the data you will be using in your report! In Power BI less is more!)

🞂 Build your data model:

Creating a great data model is one of the most important tasks that an analyst can perform in Power BI. By doing this job well, you help make it easier for people to understand and use your data, which will make building valuable Power BI reports easier for them and for you.

The idea in this step is for you to design a data model that is intuitive, high-performing and simple to maintain. To achieve this, you should aim for a star schema model.

What is a schema?

Often, data is stored in database tables which have relationships with each other. A schema is simply a group of related tables.

What is a star schema?

A star schema is the simplest form of a dimensional model, in which data is organised into fact tables and dimension tables. A dimension table contains reference information about the fact table, such as date, product, or customer. A star schema is diagrammed by surrounding each fact table with its associated dimensions tables. The resulting diagram resembles a star:

Image from: Power BI Basics of Modeling: Star Schema and How to Build it - RADACAD

So, what should I do now that I know what a star schema is? You should start building the relationships between your tables, aiming to get a star schema in the end!

In simplistic terms, these relationships will work as an Excel "VLookup".

Learn more about Start Schemas:

Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

Power BI Basics of Modeling: Star Schema and How to Build it - RADACAD

Where to start:

Use Modelling view in Power BI Desktop - Power BI | Microsoft Docs

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Docs

Learning paths:

Design a data model in Power BI - Learn | Microsoft Docs

By the end of this step, you should have:

> A relational data model (tables related to each other through relationships) - preferably you should have a star schema model

🞂 DAX measures:

In this step, we are going to create the business logic behind the figures we want to present in our report using DAX.

Data Analysis Expressions (DAX) is a programming language that is used throughout Microsoft Power BI for creating calculated columns, measures, and custom tables. You can use DAX to solve a number of calculations and data analysis problems, which can help you create new information from data that is already in your model.

The DAX topic is so huge I decided to keep it simple here. If you want to get to the depths of DAX, Alberto Ferrari and Marco Russo from SQL BI are your guys! Trust me!

Where to start:

DAX basics in Power BI Desktop - Power BI | Microsoft Docs

DAX Patterns

Learning paths:

Introduction to creating measures using DAX in Power BI - Learn | Microsoft Docs

By the end of this step, you should have:

> All the business logic (DAX measures) for the figures/values you are planning to use/present in your report


2) Report design

Report design is not just about adding charts and slicers, it's about presenting information. Always think about how you're going to present the information and align your report with the branding of your company (good looking reports makes end users happier!).

🞂 Branding

Choose colour theme

One of the key things when creating your Power BI report design is the colour scheme/theme. Colours, when used correctly, can make an average report look very compelling and insightful!

Build your Power BI colour theme

Power BI allows you to import themes, or change it directly in Power BI Desktop. To know how to change your colour theme, check this link.

Use the Power BI Theme Generator to create customised themes.

Get company logos, icons and related images

Having logos of your organisation in your report doesn’t sound like a “big deal”. However, the logos, in combination with the appropriate colour theme, are the best way to show the brand identity of your organisation in your reports. They will also look a lot nicer!

Company images are good for:

>Page background (make sure the image is faded so the report is easier to read – bright colours and images will take away the attention to important KPIs etc, will make the report difficult to read)

> “Home pages” – in this case you can use the original image without fading as this pages tend to have just the index, little information displayed

Icons are good for:

> Visual reading, interpretation and highlighting of values