Power BI Report Building Guidance

Some guidance on building effective dashboards in Power BI. There is only a loose order to this guide, but if you'd like me to expand on anything just let me know. Please like and please share if you find this useful.

Ensure that your report has some form of 'story' to it

Roughly speaking this would mean that the earlier tabs are less detailed and might contain more kpis whilst the later ones allow for further investigation of unusual areas.

Grain

Before you start understand how deeply you would like to go in terms of the lowest grain of your model. What is the most detailed level that you would like to go to? In my case I often decide that I would like to understand the deepest grain, however that does not need to be at the centre of your fact table. Careful use of the Summarize function can often mean that you can have the best of both worlds. i.e. a Summarised table using the summarize function as the central fact table and a star schema to a detailed fact table.

Combine Visuals and Tables

Another personal point, but I feel more comfortable presenting the information in this way as users get a very strong feeling as to the integrity of the model that you have built. This is particularly the case when they interact with the model by clicking on a given chart, whether it is your 0-30 day debt, your gender split or profit by business unit. Ultimately this is likely to lead to a better take up of your product.

Let the Data Express Itself

Power BI is a new product and a new type of tool for many people and very often you will find yourself with a set of requirements prepared by that have predefined thoughts about what the data must be telling them. Quite often they are right, but don't be afraid to see if there are simple patterns that can be exposed just by dragging a few fields into a visual.

Be a Student

You can be quite successful with only a little knowledge of Power BI. However, don't expect to by truly and deeply successful as an analyst unless you have a strong foundation of PowerQuery and DAX skills. Competence with these (PowerQuery to extract transform and load your data into a set of tables, and DAX to perform a range of calculations over them) is gamechanging and transforms your ability to deliver value with the product.

This is an old but still useful course on using Power BI https://courses.edx.org/courses/course-v1:Microsoft+DAT207x+6T2016/course/

Be Creative

I'm a big fan of simple bar and line charts, combined with a nicely formatted table, but some of the custom visuals can be extremely effective such as the hierarchy slicer shown here:- https://store.office.com/en-us/app.aspx?assetid=WA104380820&ui=en-US&rs=en-US&ad=US&appredirect=false

The ribbon slicer is also a very neat way of expressing positions over time (say in a football league, but any other league for that matter)

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-ribbon-charts/

Play, Play, Play

We all learned as children through play and this tool should be no different. Personally, it's very difficult to learn this topic in an abstract fashion. It is far better to take something you understand and visualise that as the errors are more obvious.

The link below is to an attempt to make Power BI more audio visual

https://app.powerbi.com/view?r=eyJrIjoiN2IxMjlkNmEtMWRiNy00ZGYxLWFhYTItNGRlYWEzMmMxMTE2IiwidCI6IjFkOTEwYzM3LWE0MjktNGYzNS1iYTFiLTM3ZDViMjNmZTRhMSIsImMiOjh9

The following is my exploration of how to perform vehicle lease calculations. Whilst it's not the most interesting of topics I'm aware that from an accounting perspective this simplifies what are relatively complex calculations. On the face of the report it appears straightforward however under the bonnet this is a very powerful demonstration of how to use functions to power up your analytics. I'll spare you the details on this occasion.

https://app.powerbi.com/view?r=eyJrIjoiZTNiNjhjNWQtMWNiNi00NDE0LWFhOTAtNmIxM2M2NzQ4YjJjIiwidCI6IjFkOTEwYzM3LWE0MjktNGYzNS1iYTFiLTM3ZDViMjNmZTRhMSIsImMiOjh9

This report here was created when we were looking for a puppy. I still enjoy just how well focussed it was on our needs at the time.

https://app.powerbi.com/view?r=eyJrIjoiOWUxZDdkNmItNDU1Zi00YTdjLThmMDYtYmZjODU5MWI2ZmQ0IiwidCI6IjFkOTEwYzM3LWE0MjktNGYzNS1iYTFiLTM3ZDViMjNmZTRhMSIsImMiOjh9

Every 'playtime' report that I have created has moved me several steps on, partly because I played so many stakeholder roles throughout the process. I've also been able overcome the technically obstacles along the way and I haven't been hampered by waiting on anything in order to realise my visions.

FAIL

First Attempt In Learning

You won't achieve success without the many micro fails along the way. They can be extremely frustrating, especially when you know you're about 30 keystrokes from success.

My stand out story on this was very much around dynamic row level security which was uber essential for our reporting solution. It took me a number of days with the help of a colleague, but we got there in the end (and it was about 300 keystrokes!)

Don't try and get Power BI to do things it's not built for

Power BI is not made for printing unless the reports are heavily tailored, which generally compromises the overall effectiveness of the report. Custom visuals generally do not print at all.

Also, don't invent and propose visuals that Power BI doesn't produce - instead work with the visuals and custom visuals that are already available. There are many examples where competitor products are stronger in a particular area, so don't necessarily try and compete with them in these areas head to head.

Power Query/M Competence

Power Query/M - A little competence in this area can go a long way. The most important features are as follows:-

• Column Splitting
• Assigning data types to columns (Date, Datetime, number)
• Creating conditional columns
• Merging with other queries
• Filtering
• Reviewing the formula bar - it's the first step to understanding the language
• Navigating through the steps
• Find and replace. And finally....
• Unpivot - possibly the most important feature as this is reshapes your data to a format that is generally acceptable to a data model

The level beyond this involves writing efficient queries that refer back to earlier steps and writing functions, but that is for another day.

Summarize() those tables

Once you have landed your tables into the model summarize can enable taking a very long table to be aggregated in a way that make it possible for you to ensure that you do not end up with tables containing many to many relationships. A combination of summarize filter and union can be extremely effective in this regard.

Here's a good link for this https://www.youtube.com/watch?v=-Ola264bKXk

This is one of my videos on the same subject covering Bradford Factor 

Create dummy measures table

Measures are very poor companions to calculated columns. Calculated columns live in the table to which the relate, but measures can exist anywhere. When calculated columns and measures appear in the same table it is quite frankly confusing. It is far simple to house all your measures in one empty home table and leave it at that.

The process for this is as follows:- Home Tab, EnterData, Rename Table from 'Table1' to Measure. From now on put all your measures in here.

Design your model backwards

To begin with you may have a clearer idea as to what you want your visuals to look like than the data model that is required to support them. With that in mind I have found it can be helpful to build a prototype that focuses on the visuals plus the worlds most basic table to support this. Typically, it's better to put the table into excel as you can build and test, then build and test iteratively. A few cycles of this and the shape of the underlying data model that you require will become more apparent.

Data Location

Housing your detailed facts should almost always be in a database, but you will inevitably come across those situations where extra tables that assist with enhancing your model are required.

First prize is to have your data in a data warehouse, ideally in the cloud, however these are often difficult to updated.

Second prize is to house your data in a sharepoint list as it combines well with Power BI, in particular from a refresh perspective. It can be a great way of housing permission related items as you get a very good audit trail at a line item level and you do have the option of making use of Office 365 in the form of Flow and PowerApps. Generally speaking it is good where the lines of data are entered manually rather than in bulk.

Third prize is to put your ancilliary data into excel in a sharepoint site dedicated to your project. The problem with excel in these circumstances is that it is less easy to lock down the shape of the spreadsheet. Nevertheless it is an approach that I have taken and would recommend, just make sure you limit the number of authors and keep yourself to just 1 sheet.

Beyond that you can create a table which is effectively a Json file that sits within the PBIX file - you can go up to roughly 500 rows with this approach. It's useful but bear in mind that if you do want to update it you will need to pull it down from the web location amend it and reupload it again. The process to create this is Home->EnterDate then just start entering data, or better still just paste it in.

Finally, you can put your data into excel on your server and refresh using the Power BI Gateway.

Use the PBI Gallery for getting ideas

The Power BI Gallery contains very useful ideas from the perspective of presentation. I would recommend that you take a look around as quite often there are clever uses of the tools available. See link below:-

https://community.powerbi.com/t5/Data-Stories-Gallery/bd-p/DataStoriesGallery

Put borders on all your visuals

It's a personal point but it is my preference, however I think with further advancement I may relax on this somewhat!

Reports seem more effective than dashboards

So don't focus on dashboards. The reports themselves are dashboard - I don't find that the dashboards are that useful and only act as a portal to the report itself. I think the main reason for this is that the models that I have prepared have all been for a single purpose and the underlying datamodel is effectively a single star schema. Additionally, it is very difficult to 'read' the dashboard/report/datamodel structure of a workspace when lots of things have been thrown at it.

Filters

Careful and tailored use of filters are very important and arguably move your reports from being relatively static to something that can be truly explored.

Page Filters

If you introduce fields into page filters it means that you can hyperlink to a filtered version of the page. This is the case even where the filter is empty. Some details for this are shown via the following link :-

https://powerbi.microsoft.com/en-us/documentation/powerbi-service-url-filters/

Increase the canvas size

It's a very simple way of increasing your screen real estate, once you get over the fact that users will need to scroll around your report. I would typically only increase the length, not the width.

Use Mapping

This is one of the most powerful aspects of the product and certainly something that distinguishes it from a normal excel experience. It may feel strange at first, but it is worth persevering with.

Time Intelligence

Time intelligence comes 'out of the box' however you need to be aware that when you use it a hidden table is created every time that a date field is encountered and this can cause your file to be surprisingly large as a rule of thumb you're probably adding about 1mb per date field. The biggest annoyance is that as a result that it takes a long time to upload it to the Power BI service. It's never been a problem on my mini projects but it is relevant when moving to something more production based.

So that's all from me for the moment please like it if you've found it useful. Do drop me a line or a comment and I'll see what I can do to help out.